G
Guest
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.
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.