Selecting cells in Excel using variables
Selecting cells in Excel using variables
(OP)
I need to select a range of cells from (1,1) to (i,8), where i is variable computed during the execution of my VBA routine.
Microsoft documentation (and my book) shows a way you can select the cells if the co-ordinates are hard-coded, e.g. range("a2:b3").select
However, how do you do that if you have a variable? I will need to convert i to an alphabet. Fine if i is less than 26, what if it is more?
I also tried range((1,1),(i,8)).select but that returned a syntax error.
Does anybody know the right way to get round it? Many thanks.
Microsoft documentation (and my book) shows a way you can select the cells if the co-ordinates are hard-coded, e.g. range("a2:b3").select
However, how do you do that if you have a variable? I will need to convert i to an alphabet. Fine if i is less than 26, what if it is more?
I also tried range((1,1),(i,8)).select but that returned a syntax error.
Does anybody know the right way to get round it? Many thanks.





RE: Selecting cells in Excel using variables
Dim StartCellLtr As String
Dim StartCellNum As Integer
Dim EndCellLtr As String
Dim EndCellNum As Integer
Dim TheRange As String
StartCellLtr = "A"
StartCellNum = 1
EndCellLtr = "D"
EndCellNum = 4
TheRange = StartCellLtr & Trim(StartCellNum) & ":" & EndCellLtr & Trim(EndCellNum)
Range(TheRange).Select
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein
RE: Selecting cells in Excel using variables
Range(Cells(1, 1), Cells(i, 8)).Select
RE: Selecting cells in Excel using variables
Sub SelectionExample()
Dim iRow As Long, iCol As Long
iRow = 5
iCol = 6
With Sheet1
Set a = .Range(.Cells(iRow, iCol), .Cells(iRow + 4, iCol + 2))
End With
a.Select
End Sub
The example selects a range 5 rows by 3 colums on Sheet2
RE: Selecting cells in Excel using variables
You can use various constructs, such as
For I = 1 to 3
ExcelFileObj.Worksheets(WkSht).Range("A" & J).Value = J * 2
Next
or, since I like to use named ranges:
TestValue = ExcelFileObj.Worksheets(1).Range("RangeName").Value
RE: Selecting cells in Excel using variables
CODE
RE: Selecting cells in Excel using variables
Range(Cells(1, 1), Cells(i, 8)).Select
worked wonders.