simple question

[bob]

The formula =ROW(G11) returns the value 11

I need VBA Code that will return the row value of the
active cell. For example, I want the value of Sheet1:A1
to be the current active cell value (i.e if the current
active cell is G11 the value returned for Sheet1:A1 would
be 11).

 

[Robin Hammond]

Bob,

this is not fully tested but seems to do the job WHEN the sheet is
calculated. It won't change if you simply change the selection.

Public Function ActiveCellAddress() As Variant
Dim rngCell As Range
Application.Volatile

On Error Resume Next
Set rngCell = ActiveCell
On Error GoTo 0

If Not rngCell Is Nothing Then
ActiveCellAddress = rngCell.Address
Else
ActiveCellAddress = CVErr(xlErrNA)
End If
End Function

If you need something that changes every time the selection is changed then
put this in the code behind the worksheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Target Is Nothing Then
Range("a1").Value = ActiveCell.Address
'or if you want the whole selected range
'Range("a1").Value = Target.Address
'or if you want the top left cell in the selected range
'Range("a1").Value = Target(1,1).Address
End If
End Sub


Robin Hammond
www.enhanceddatasystems.com

 

[Norman Jones]

Hi Bob,

Try:

ActiveCell.Row

 

[Ron Rosenfeld]

The formula =ROW(G11) returns the value 11

I need VBA Code that will return the row value of the
active cell. For example, I want the value of Sheet1:A1
to be the current active cell value (i.e if the current
active cell is G11 the value returned for Sheet1:A1 would
be 11).

Right click on the sheet tab and select View Code.

Paste the following code into the window that opens:

=================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
[a1].Value = activecell.Row
End Sub
=====================

This will work on the worksheet that you selected.


--ron