Selecting a Range using a Variable

[RaunLGoode]

I want to go through a table row by row and, when a condition is met, Highlight a range of cells in that row. (RowX,1) through (RowX,34)
I can select the entire row (Row 1) or a specific Range (A1:A34) but I can't get the syntax right for a range described with a variable. Can somebody out there help?

 

[boblarson]

Let's say you have two variables

intFrom and intTo

To refer to a range in Column A to Column D, you can use them in the range by using:

Activesheet.Range("A" & Cstr(intFrom) & ":d" & Cstr(intTo))

 

[HaHoBe]

Hi, RaunLGoode,

Dim X As Long
Dim intCol As Integer
X = ActiveCell.Row
intCol = 1
Range(Cells(X, intCol), Cells(X + 2, intCol + 5)).Interior.ColorIndex = 6

According to my knowledge (which indeed is very limited) there is no need to convert numeric expressions to strings for a range object.

Ciao,
Holger

 

[boblarson]

Referring to column number, you may not need to convert but if you want to use the column letter as a designator, then you do.

 

[RaunLGoode]

Tell me More.

In this case, I do not need to use a column letter as a designator. But for futute consideration, how do you convert a numeric expressions to a string?
I assume it requires more than defining the variable as a string type.

 

[boblarson]

Cstr(YourNumericValue) will turn it into a string

 

[RaunLGoode]

Thanks

As always Bob ...and Holger, you're a tremendous help to us newbies.

 

[HaHoBe]

Hi, RaunLGoode,

first of all: if you found a solution that fits your needs stay with that - no matter what arguments I may come up with...

A Range object understands the numeric instruction given quite well. As the String starts with a letter for the Column Excel (at least 97 through to 2003) is smart enough to convert the numeric expression following that letter(s) (and furthermore parts) into the string argument needed.

The Cells object always surprises me to work with the following code as well

Dim X As Long
X = ActiveCell.Row
Range(Cells(X, "A"), Cells(X + 2, "F")).Interior.ColorIndex = 6

And to me with the Cells object there´s no need to convert a numeric expression into a string: you can either use the numeric or the letter of the Column whichever fits in best.

Ciao,
Holger

 

[boblarson]

And to me with the Cells object there´s no need to convert a numeric expression into a string: you can either use the numeric or the letter of the Column whichever fits in best.

Ciao,
Holger

While it is not NECESSARY to convert, it is, for some people, easier to think in column letters instead of column numbers. I, for one, am one of those people for which P24 has more meaning than having to remember that for column P I need column number 21. So, it does have a lot to do with personal preferences and the way the programmer himself thinks in terms of when using spreadsheets.

 

[HaHoBe]

Hi, Bob,

then it seems to me like a code

Cells(24, "P").Select

isn´t working with english/american versions of Excel (it´s working fine for me but I´m using the german versions of the application). Is that correct?

Ciao,
Holger

 

[boblarson]

You can't use letters with the Cells() syntax. But, you can use

ActiveSheet.Range("B2:d4").Select

So, in variable world it would be:

ActiveSheet.Range("B" & Cstr(intRow) & ":d" & Cstr(IntEndRow)).Select

Since the Range object in this case is referencing a string "B2:d4" the numbers have to be converted to strings.

Again, there's a whole lot of different ways to get at the same result when programming, so it can come down to personal preferences.

 

[HaHoBe]

Bob,

please do me a favour and answer the question if you tested the code - I doubt that very much according to what you quoted about the Excel Help. If you haven´t done it just test it and then give me your answer - I wonder the result of that.

btw: I know the syntax for Ranges as well as Cells, I can assure you that. And I know what´s written in the Help Section (at least in the German version of it) and what the code above does. And in this case (at least for the German part of Excel) old reminiscences come through and perform a little magic that´s not like the Help states...

Ciao,
Holger

 

[boblarson]

I didn't just go into help. I went and wrote the code and when I saw the ColumnIndex piece I did, erroneously, assume that it would only take a numeric value. It is true that you can use "P" encapsulated in quotation marks.