can I use GO TO results in a formula?

[Guest]

start point: formula in col. B
directions: go to col. A, step upward 'til reach non-blank cell
end point: use contents of $A# inside the formula

I can physically find the required data using Go To (or even [End] +
arrow-up key)
but how would one write a macro to do this?

 

[Chip Pearson]

Try something like

Dim Rng As Range
Dim S As String
Set Rng = Cells(Rows.Count, "A").End(xlUp)
S = CStr(Rng.Row)
Rng(1, 2).Formula = "=$A$" & S

Or, more compactly,

Cells(Rows.Count, "A").End(xlUp)(1, 2).Formula = _
"=$A$" & CStr(Cells(Rows.Count, "A").End(xlUp).Row)



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"having eXceLed, I now want aXeS"
message
news:[email protected]...

 

[Tom Ogilvy]

Range("B100").Select
set rng = Cells(ActiveCell.Row,1).End(xlup)
activecell.Formula = "=Sum(A1:" & rng.Address(0,0) & ")"