Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

  • Congratulations waross on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

Selecting cells in Excel using variables 1

Status
Not open for further replies.

Grafton54

Computer
May 9, 2005
11
GB
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.
 
Replies continue below

Recommended for you

Here is an example that might help.

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
 
Use the Cells method to select a range when the extents of the range are variable. Here's an example

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
 

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
 
Thank you for all your replies. Using

Range(Cells(1, 1), Cells(i, 8)).Select

worked wonders.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top