Defining Range with Variables

[EL_SB]

I have a changing range always composed of 1 column. I have the start and end points determined in a loop as L and J.

How do I refer to that Range if the column is always "I"?

I tried SummarySheet.Range(Cells(9, L), Cells(22, J)) but no luck.

I would like to feed this Range into the COUNTIF function to search for a specific string.

 

[AKR22]

try:

ActiveSheet.Range("I" & L & ":I" & J).Value

 

[James006]

Hi,

If you are using Cells(row,col) ... just use the column number ... ie. for Col I : 9 .. and for Col L : 12 ...

HTH

 

[Rick Rothstein]

If you are using Cells(row,col) ... just use the column number ... ie. for Col I : 9 .. and for Col L : 12 ...

You can also use the letters, but those letters must be quoted (otherwise VB thinks they are variable names).

 

[Rick Rothstein]

How do I refer to that Range if the column is always "I"?

I tried SummarySheet.Range(Cells(9, L), Cells(22, J)) but no luck.

Try it this way...

SummarySheet.Range(Cells(9, "I"), Cells(22, "I"))

Of course, doing it that way does not use L and J. If L and J are variables containing the start and end rows for the range, then what you want is this...

SummarySheet.Range(Cells(L, "I"), Cells(J, "I"))