Referring to a Range using Cells(Row,Column) system
 

Is there a way to define a range using the Cells(R,C) function in VB ? I
wrote several macros years ago that I want to update. This is how I've been
handling the references -

Dim ALPHA(27)

ALPHABET = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
For X = 1 To 26
ALPHA(X) = Mid(ALPHABET, X, 1)
Next X

when I get further out on the spreadsheet I have -
For X = 1 to 9 ' first letter of column A - I
For Y = 1 to 26 ' second letter A - Z

COL = ALPHA(X) & ALPHA(Y)

Then I do some calc's to find the height of the range and put together the
syntax

HRRANGE = COL & H & ":" & COL & 59 ' ex. AI30:AI59

Then I select that range in a subroutine and change the color, Red
(HRRANGE), or give it borders, Borders (HRRANGE), depending on situation.
The macros run fine now but I was hoping I could simplify the coding.
I would need to select the range between Cells(30,35) and Cells(59,35) and
then run one of the macros.

Referring to a Range using Cells(Row,Column) system
 

Dim i as long
With Worksheets("Sheet1")
for i = 30 to 59
if .cells(i,35) = "something" then
.cells(i,35).Interior.colorindex = 3
else
.cells(i,35).Inteior.colorindex = 3
end if
Next
End With


so no, you don't have to use the Letters to refer to a column. Also, you
don't need to select anything to work with it (in most cases).

--
Regards,
Tom Ogilvy

"Rich J" wrote in message
...
Is there a way to define a range using the Cells(R,C) function in VB ? I
wrote several macros years ago that I want to update. This is how I've
been
handling the references -

Dim ALPHA(27)

ALPHABET = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
For X = 1 To 26
ALPHA(X) = Mid(ALPHABET, X, 1)
Next X

when I get further out on the spreadsheet I have -
For X = 1 to 9 ' first letter of column A - I
For Y = 1 to 26 ' second letter A - Z

COL = ALPHA(X) & ALPHA(Y)

Then I do some calc's to find the height of the range and put together
the
syntax

HRRANGE = COL & H & ":" & COL & 59 ' ex. AI30:AI59

Then I select that range in a subroutine and change the color, Red
(HRRANGE), or give it borders, Borders (HRRANGE), depending on situation.
The macros run fine now but I was hoping I could simplify the coding.
I would need to select the range between Cells(30,35) and Cells(59,35)
and
then run one of the macros.

Referring to a Range using Cells(Row,Column) system
 

Thanks Tom. I didn't think about addressing each cell in the range
individually. I'm changing my routines and eliminating dozens of lines of
code in the process.


"Tom Ogilvy" wrote:

Dim i as long
With Worksheets("Sheet1")
for i = 30 to 59
if .cells(i,35) = "something" then
.cells(i,35).Interior.colorindex = 3
else
.cells(i,35).Inteior.colorindex = 3
end if
Next
End With


so no, you don't have to use the Letters to refer to a column. Also, you
don't need to select anything to work with it (in most cases).

--
Regards,
Tom Ogilvy

"Rich J" wrote in message
...
Is there a way to define a range using the Cells(R,C) function in VB ? I
wrote several macros years ago that I want to update. This is how I've
been
handling the references -

Dim ALPHA(27)

ALPHABET = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
For X = 1 To 26
ALPHA(X) = Mid(ALPHABET, X, 1)
Next X

when I get further out on the spreadsheet I have -
For X = 1 to 9 ' first letter of column A - I
For Y = 1 to 26 ' second letter A - Z

COL = ALPHA(X) & ALPHA(Y)

Then I do some calc's to find the height of the range and put together
the
syntax

HRRANGE = COL & H & ":" & COL & 59 ' ex. AI30:AI59

Then I select that range in a subroutine and change the color, Red
(HRRANGE), or give it borders, Borders (HRRANGE), depending on situation.
The macros run fine now but I was hoping I could simplify the coding.
I would need to select the range between Cells(30,35) and Cells(59,35)
and
then run one of the macros.