select relative reference

[timstring]

This is a really ignorant question, because I should know the simple answer. I need a command that causes a selection over a range of cells where the column varies from one sheet to another. Here's what I've tried:

ActiveCell.Offset(0, 0).Range("R[1]C[1]:R[2]C[" & LastCol & "]").Select

I know the value of LastCol. What am I overlooking?

ts

 

[CodeNinja]

You are trying to offset into a range... use a range method...

Sheet1.Range(ActiveCell.Address & ":" & ActiveCell.Offset(2, LastCol).Address).Select 'change the 2, LastCol to whatever you want...

 

[timstring]

Thanks for the speedy reply. Your tip did the trick.

Now, another related puzzle for you:

How do I stop a loop when the last cell is reached? Such as

Loop Until ActiveCell.Offset (2, LastCol) = something (Last Row, Last Col)

?

 

[timstring]

Here's the son of the 2nd puzzle: How do i check the value of a cell without activating the cell?

 

[CodeNinja]

How do I stop a loop when the last cell is reached?

That depends on the loop... a while loop will end when the condition is met... a for next loop will end when you have reached the end of the for... So create a condition where your loop ends when the last cell is reached...

LastCell = Range.cells.count
For i = 1 to LastCell
Do stuff
next i

Here's the son of the 2nd puzzle: How do i check the value of a cell without activating the cell?

While you can get a value from an active cell - activecell.value, you can also get the value from a cell by calling it... - sheets("SheetName").cells(1, "A")

 

[timstring]

While you can get a value from an active cell - activecell.value, you can also get the value from a cell by calling it... - sheets("SheetName").cells(1, "A")

So, how do I use the 'cells' statement with a relative reference? I need the command to go to the cell one row down and LastCol - 1? I have tried several combinations.

 

[CodeNinja]

If you wish to move the active cell, you will have to select that cell... What you are describing would be best done with activecell.offset...

Activecell.offset(1,lastCol - 1 - activecell.column).select

You could also do:
sheets("Sheet1").cells(activecell.row + 1, lastcol - 1).select

Or if you do not wish to move to the cell, you could just get the value...
variable = Activecell.offset(1,lastCol - 1 - activecell.column) or variable = sheets("Sheet1").cells(activecell.row + 1, lastcol - 1)

of course, change sheet1 to whatever your sheet name is...