reference a range using a variable

how do i reference A1 using my long variable 'i'

sub hi()
dim i as long
i = 1
Range("A1")select
End sub

I want something like

Range(i & "1").select

but I need to get the column letter of cells(1, i) somehow.

Wrong way round, there - A is the column, not the row:

Cells(1,i).Select

hi niko79542, there is a couple of ways for Range syntax:

1. Range("a1") - using variables: Range(x & y), where x = "a" and y=1
2. Range(Cells(i,n),cells(m,k)) where i,m - row identifier, n,k - columns

Noooo!!! Dont go away!!! Yes I understand these options, however my variable i is a number, and I cant use the

cells(1, i)

method. Because I get an error...

This here works for me...

Workbooks("name.xlsm").Worksheets("Sheet1").Range("A1:A10").copy Destination:= Workbooks("name.xlsx").Worksheets("Sheet1").Range("A1:A10")

This does not work for me...

Workbooks("name.xlsm").Worksheets("Sheet1").Range(cells(1,1),cells(10,1)).copy Destination:= Workbooks("name.xlsx").Worksheets("Sheet1").Range(cells(1,1),cells(10,1))

...I figured I would save you guys the long winded reason as to why I am not using cells(1,i)...But here it is..

---------- Post added at 04:48 PM ---------- Previous post was at 04:46 PM ----------

Tchh... One really complicated way is

Left(Cells(1, symbol).AddressLocal(RowAbsolute:=False, ColumnAbsolute:=False), 1)

Try this:

Sub Test
Dim i As Long
Dim rngSource As Range, rngDestination As Range

i=10

With Workbooks("name.xlsm").Worksheets("Sheet1")
  Set rngSource=.Range(.Cells(1,i),.Cells(10,i))
End With

With Workbooks("name.xlsx").Worksheets("Sheet1")
  Set rngDestination=..Cells(1,i)
End With

rngSource.Copy Destination:=rngDestination

End Sub

Didn't get a change to try this out last night, but this is what I was looking for, thanks Andrew

So, I did scrounge up a solution. thanks again Andrew.

is the ..Cells in your code a shortcut? or did you just not bother typing out Range().

Not sure you ever revisit this thread again...but if you do =P.

nick

Inside a With block you use a period at the start of an object to show it's a child of the object named in the With.

So:

With ThisWorkbook.Sheets(1)
  MsgBox .Range("A1").Value
End With

Means that you're referring to the Range A1 on sheet 1 of this workbook.

The same block of code without the period:

With ThisWorkbook.Sheets(1)
  MsgBox Range("A1").Value
End With

Means you're referring to range A1 on the active sheet, so the two blocks of code can return different results depending on which sheet is active when code is run.

This was what was causing you problems, when you were specifying:

Workbooks("name.xlsm").Worksheets("Sheet1").Range(cells(1,1),cells(10,1)).copy Destination:= Workbooks("name.xlsx").Worksheets("Sheet1").Range(cells(1,1),cells(10,1))

All 4 references to Cells refer to objects on the active sheet, not necessarily on the sheets you're trying to copy from or paste to, and this was causing Excel problems. You were effectively saying, "Make a range on this sheet, from cell A to cell B on another sheet".

All my code did was precisely specify which cells I was referencing.

Does that make sense? It's a bit counter-intuitive, but such are the strange byways of Excel coding

Ahh....That totally makes sense.

What I ended up doing was using Cells.Address to convert the column number to a letter, and then it was easy to go from there.

Dim My as String
My = Left(Cells(1, i).Address(1, 0), InStr(1, Cells(1, i).Address(1, 0), "$") - 1)

I thought I tried your way of referring to ranges using the period before the cell reference, but I got an error. I'll try it again now with your explanation in mind.

Nick