variable cells

[Laura]

i previously posted a question asking how to assign the value of a cell to a variable. now someone was kind enough to answer that question, but the answers i tested out only worked with constant variable references. in my situation, the row number must itself be a variable. is there a way to assign the value of the cell with row unknown and column a to a variable?

[Damon Ostrander]

Hi Laura,

If I understand your question correctly, you can assign a cell value to a variable, assuming you know the cell is in column A and you have a variable, say iRow which is computed, that contains the row number:

VariableName = Worksheets("Sheet Name").Cells(iRow,"A").Value

or

VariableName = Worksheets("Sheet Name").Cells(iRow,1).Value

which is more efficient.

If the worksheet is the active sheet, then this can be shortened to:

VariableName = Cells(iRow,1).Value

Note: be sure to always declare your row variable (iRow in this example) as Long. Otherwise you won't be able to go past row 32767.

[NateO]

In case you mean that the row #'s a moving target, if you want the last cell in column A:

Sub ls()

Dim n As Range

Set n = [a65536].End(xlUp)

End Sub

Now you can work with n, e.g., n.value, n.Address(False, False), etc....