Macro to Formula References
 

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

Macro to Formula References
 

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
DTHIS
www.nickhodge.co.uk


"K1KKKA" wrote in message
ups.com...
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

Macro to Formula References
 

Thanks Nick,

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


Steve


Nick Hodge wrote:
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
DTHIS
www.nickhodge.co.uk


"K1KKKA" wrote in message
ups.com...
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

Macro to Formula References
 

Nick,,

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


Steve


K1KKKA wrote:
Thanks Nick,

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


Steve


Nick Hodge wrote:
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
DTHIS
www.nickhodge.co.uk


"K1KKKA" wrote in message
ups.com...
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

Macro to Formula References
 

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
DTHIS
www.nickhodge.co.uk


"K1KKKA" wrote in message
ups.com...
Thanks Nick,

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


Steve


Nick Hodge wrote:
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
DTHIS
www.nickhodge.co.uk


"K1KKKA" wrote in message
ups.com...
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