Cell reference based on calculation

[Guest]

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.

 

[Dave O]

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

 

[Guest]

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

 

[topola]

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 Tool>Macro>Visual
Basic Editor. Then: Insert>Module. Within a module just copy the
following instruction.

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