reference a range using a variable

[niko79542]

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

Please Login or Register  to view this content.

I want something like

Please Login or Register  to view this content.

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

[vandan_tanna]

Please Login or Register  to view this content.

[Andrew-R]

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

Please Login or Register  to view this content.

[watersev]

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

[niko79542]

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

Please Login or Register  to view this content.

method. Because I get an error...

This here works for me...

Please Login or Register  to view this content.

This does not work for me...

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

[Andrew-R]

Try this:

Please Login or Register  to view this content.

[niko79542]

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

[niko79542]

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

[Andrew-R]

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:

Please Login or Register  to view this content.

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

The same block of code without the period:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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

[niko79542]

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.

Please Login or Register  to view this content.

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