Cell reference based on calculation
 

In my worksheet I have a calculation (count of records). I want the macro to
send by cursor / cell reference that many rows down in the spreadsheet.
However, I want to do this in a blank row, so "Selection.End(xlDown).Select"
doesnt work. Can someone help be simply move my cursor to that location?
(Yes, I'm an amateur).

Example:
I have data in A2 through B50
Cell A1 = count(A2:b50)...this calculates 98.
I want the cell reference to move to C98.

I'm beyond ignorant, and don't know how to program that into my macro. Any
help would be appreciated. Thanks.

When you say I want the cell reference to move to C98<< do you mean
you want the cell *pointer* to move? If so, then this line will move
the pointer to that cell:

Range("c" & Range("a1").Value).Select

Assuming that the column might change in the future, the following is a
flexible way of finding a cell

Sub test()
Dim r As Long, nr As Long, row As Long
Dim col As Integer, nxtcol As Integer
row = Range("A1")
col = Range("a1").CurrentRegion.Columns.Count
nxtcol = col + 1
Cells(row, nxtcol).Select
End Sub

you could also use row=RAnge("a1").currentregion.rows.count

regards
Peter

"scott_ross_3" wrote:

In my worksheet I have a calculation (count of records). I want the macro to
send by cursor / cell reference that many rows down in the spreadsheet.
However, I want to do this in a blank row, so "Selection.End(xlDown).Select"
doesnt work. Can someone help be simply move my cursor to that location?
(Yes, I'm an amateur).

Example:
I have data in A2 through B50
Cell A1 = count(A2:b50)...this calculates 98.
I want the cell reference to move to C98.

I'm beyond ignorant, and don't know how to program that into my macro. Any
help would be appreciated. Thanks.

Hi,
Try using the following macro:

Sub Macro1()
Dim Counter As Integer
Counter = Selection.Cells.Count
Cells(Counter, 3).Select
End Sub

You can open Visual Basic panel using Alt+F11 or ToolMacroVisual
Basic Editor. Then: InsertModule. Within a module just copy the
following instruction.

Regards,
Tomek Polak
www.vba.blog.onet.pl