Macro to Formula References

K

K1KKKA

Hi,

Am new to writing my own macros and am struggling with the following.

I have a spreadsheet that has multiple amounts of data and each row
starts with a date, when i enter the Date into Cell "A1" i would like
to run a macro that reads this date and jumps to the Row that has the
corresponding date and data,

Is this possible??


Any help would be appreciated



Steve
 
N

Nick Hodge

Steve

You could use a worksheet_change event, like that below (To implement, right
click on the sheet tab and select 'view code...' and paste the code in the
resulting window

Private Sub Worksheet_Change(ByVal Target As Range)
Dim fnd As Range
If Not Application.Intersect(Target, Range("A1")) Is Nothing Then
Set fnd = Range("A2:A65536").Find(Target.Value)
If fnd Is Nothing Then
MsgBox "Date not found", vbOKOnly + vbInformation
Exit Sub
End If
ActiveWindow.ScrollRow = Target.Row
fnd.Select
End If
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]
www.nickhodge.co.uk
 
K

K1KKKA

Thanks Nick,

worked a charm, may i ask 1 more question, can i run an event macro
from a button on the sheet?


Steve
 
K

K1KKKA

Nick,,

Please forget last reply, Event macro works fine, many thanks, no need
to run the button


Steve
 
N

Nick Hodge

Steve

You are better to put it in a standard module in the VBE (Alt+F11) and the
code would need to change to

Sub MoveToRow()
Dim fnd As Range
fnd = Range("A2:A65536").Find(Range("A1").Value)
If fnd Is Nothing Then
MsgBox "Date not found", vbOKOnly + vbInformation
Exit Sub
End If
ActiveWindow.ScrollRow = fnd.Row
fnd.Select
End Sub

IMPORTANT: In my previous reply the line

ActiveWindow.ScrollRow = Target.Row

should read...

ActiveWindow.ScrollRow = fnd.Row

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]
www.nickhodge.co.uk
 
Top