• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Creating a range $C$5:$F??? - based on a condition in $A???

NeverHappyMike

NeverHappyMike

Member
Hi

I'm working through multiple, and slightly different sheets, and trying to select a range based on a condition in a column that will not be part of the final range. I would then format the numbers within my final range.

Column A = contains number "14". (Row n)
Final range = $C$5:$F(n)

However, as I'm doing this within a VBA macro (Excel 2010) I cannot just select the range, I need to find it programmatically.

I've looked into INDEX, but cannot seem to integrate how to find the hard coded number "14".

I can imagine using a variable to store the row number and then add it to my:
Code: 
Range("$C$5:$F"n"").Select
But I'm lost in how to say find that row number based on something in Column A.

Any help very much appreciated.

Thanks
NeverHappyMike.
 
Hi Mike ,

The syntax for selecting the range based on a variable n , will be :

Range("$C$5:$F$" & n).Select

If you can upload your file with some data in it , it will be easier to provide a more helpful answer.

Narayan
 
Hi Narayan

I've attached a file.

You'll see I've highlighted the condition in column A (number 14) that will allow me to use that specific row number to create an appropriate range (yellow cells).

This range would then be formatted with a certain number format. This number format can only be applied to that range of cells, and no other.

Within my multiple worksheets the condition in Column A can appear on row 28 to row 39.

Thanks for any help you can give.

Mike.
 

Attachments

  • NeverHappyMike_Range.xlsx
    38.9 KB · Views: 4
Hi Mike ,

Sorry , but I am no closer to understanding your requirement.

Column A seems to have numbers in both tabs APW_single and APW_married.

What condition are you talking about ?

The range you have highlighted in yellow is C6:F39 ; if we assume that the endpoint 39 in this case is variable , what is the basis on which it will vary ? Will we have to look for the number 14 in column A , and select which ever row it is detected in ?

Narayan
 
Hi Narayan

Will we have to look for the number 14 in column A , and select which ever row it is detected in ?
Click to expand...

Nailed it. As both tabs are the same structure I only highlighted the first one.

I'm looking to find the number 14 in Column A and use the row it is in to help me make a selection that goes from $C$5:$F[Number 14 row number].

I have a macro that already does everything I need it to do, but I just need to format a range of numbers before it is perfect. Seeing as I need to do this to a lot of sheets I thought I would include this formatting within the existing macro. However, I'm struggling to find a way to select the appropriate range for each slightly different worksheet.

I hope that helps a bit more.

Thanks
Michael.
 
Narayan. Wonderful. It does exactly what I was failing to do.

Looking at how you've done it I realise that my aspirations were well above my abilities. Thank you so very much.

And also a big thanks to Chandoo's forum.

Mike.
 
You must log in or register to reply here.
Back
Top