Hi, gurus of Excel
This is the code I am using:

Sub Go2Date()
Dim myFind As Integer
Dim rng As Range
myFind = ActiveSheet.Range("A2").Value
Set rng = ActiveSheet.Range( _
"$A$3:$G$564").Find(myFind, _
LookIn:=xlValues, LookAt:=xlWhole)
If Not rng Is Nothing Then
Application.Goto rng, True
Else
MsgBox myFind & " not found"
End If
End Sub

What I would like it to do:
Look at the date in A3
Then find that date in the range of the calendar (A3:G564)
When found, then go to that date.

If possible, I would rather want it to go to the beginning of that row
(column A) because:
Column A = Monday, Column 2 is Tuesday ....
Under each day, there are 10 cells, which read input from another calendar
for scheduling events.
The idea is to click on the button I provided with assigned code, which
looks at the date the user wishes to see, then takes them to that date (
rather beginning of the week of that date col A) in the calendar.

Your help is greatly appreciated.
THX
Driftwood

Change this
Application.Goto rng, True
To
Cells(rng.row,1).Activate


"Driftwood" wrote in message
...

Hi, gurus of Excel
This is the code I am using:

Sub Go2Date()
Dim myFind As Integer
Dim rng As Range
myFind = ActiveSheet.Range("A2").Value
Set rng = ActiveSheet.Range( _
"$A$3:$G$564").Find(myFind, _
LookIn:=xlValues, LookAt:=xlWhole)
If Not rng Is Nothing Then
Application.Goto rng, True
Else
MsgBox myFind & " not found"
End If
End Sub

What I would like it to do:
Look at the date in A3
Then find that date in the range of the calendar (A3:G564)
When found, then go to that date.

If possible, I would rather want it to go to the beginning of that row
(column A) because:
Column A = Monday, Column 2 is Tuesday ....
Under each day, there are 10 cells, which read input from another
calendar
for scheduling events.
The idea is to click on the button I provided with assigned code, which
looks at the date the user wishes to see, then takes them to that date (
rather beginning of the week of that date col A) in the calendar.

Your help is greatly appreciated.
THX
Driftwood

Thanks,
I have implemented the change, but am receiving:
Run time error 6
Overflow

- which by the way I was receiving earlier and failed to mention - sorry
Is it perhaps because every cell in the range to be searched is filled with
a formula and not an actual value?
=IF('Daily log book'!B27<"",'Daily log book'!B27,"")

This calendar will not have any input, it is there so everybody can go in
and see what the latest status of events are - it is rather a matrix of all
inputs from associates on their calendars combined into one overview, read
from a previous WS in this WB.
Would there be something else I could try?
THX
Driftwood



"Patrick Molloy" wrote:

Change this
Application.Goto rng, True
To
Cells(rng.row,1).Activate


"Driftwood" wrote in message
...

Hi, gurus of Excel
This is the code I am using:

Sub Go2Date()
Dim myFind As Integer
Dim rng As Range
myFind = ActiveSheet.Range("A2").Value
Set rng = ActiveSheet.Range( _
"$A$3:$G$564").Find(myFind, _
LookIn:=xlValues, LookAt:=xlWhole)
If Not rng Is Nothing Then
Application.Goto rng, True
Else
MsgBox myFind & " not found"
End If
End Sub

What I would like it to do:
Look at the date in A3
Then find that date in the range of the calendar (A3:G564)
When found, then go to that date.

If possible, I would rather want it to go to the beginning of that row
(column A) because:
Column A = Monday, Column 2 is Tuesday ....
Under each day, there are 10 cells, which read input from another
calendar
for scheduling events.
The idea is to click on the button I provided with assigned code, which
looks at the date the user wishes to see, then takes them to that date (
rather beginning of the week of that date col A) in the calendar.

Your help is greatly appreciated.
THX
Driftwood

What's in that cell?

Dim myFind As Integer

Maybe the value in ActiveSheet.Range("A2") is too large for Integer types.

But I expect it to be that the value is "".

I'd use:

Dim myFind as Variant

Driftwood wrote:

Thanks,
I have implemented the change, but am receiving:
Run time error 6
Overflow

- which by the way I was receiving earlier and failed to mention - sorry
Is it perhaps because every cell in the range to be searched is filled with
a formula and not an actual value?
=IF('Daily log book'!B27<"",'Daily log book'!B27,"")

This calendar will not have any input, it is there so everybody can go in
and see what the latest status of events are - it is rather a matrix of all
inputs from associates on their calendars combined into one overview, read
from a previous WS in this WB.
Would there be something else I could try?
THX
Driftwood

"Patrick Molloy" wrote:

Change this
Application.Goto rng, True
To
Cells(rng.row,1).Activate


"Driftwood" wrote in message
...

Hi, gurus of Excel
This is the code I am using:

Sub Go2Date()
Dim myFind As Integer
Dim rng As Range
myFind = ActiveSheet.Range("A2").Value
Set rng = ActiveSheet.Range( _
"$A$3:$G$564").Find(myFind, _
LookIn:=xlValues, LookAt:=xlWhole)
If Not rng Is Nothing Then
Application.Goto rng, True
Else
MsgBox myFind & " not found"
End If
End Sub

What I would like it to do:
Look at the date in A3
Then find that date in the range of the calendar (A3:G564)
When found, then go to that date.

If possible, I would rather want it to go to the beginning of that row
(column A) because:
Column A = Monday, Column 2 is Tuesday ....
Under each day, there are 10 cells, which read input from another
calendar
for scheduling events.
The idea is to click on the button I provided with assigned code, which
looks at the date the user wishes to see, then takes them to that date (
rather beginning of the week of that date col A) in the calendar.

Your help is greatly appreciated.
THX
Driftwood


--

Dave Peterson

Hi Dave,
Thanks for the reply, but I had to leave in a hurry yesterday and can now
continue with my issue.I have implemented your change to my original code,
which now runs all the way through - to where it comes up "7/23/09 not found"
- I typed 7/23/09 in B2 - of course to trigger a response...
To your question, the value in the cell A2is this derived from this formula,
=IF(ISBLANK($B$2),$A$1,$B$2)
of which is A1= =TODAY()
and B2 is Blank - user can type in a date to search for if not "TODAY"(
which would become default).

Hope you still answer "OLD" replies...
Thanks
Driftwood


"Dave Peterson" wrote:

What's in that cell?

Dim myFind As Integer

Maybe the value in ActiveSheet.Range("A2") is too large for Integer types.

But I expect it to be that the value is "".

I'd use:

Dim myFind as Variant

Driftwood wrote:

Thanks,
I have implemented the change, but am receiving:
Run time error 6
Overflow

- which by the way I was receiving earlier and failed to mention - sorry
Is it perhaps because every cell in the range to be searched is filled with
a formula and not an actual value?
=IF('Daily log book'!B27<"",'Daily log book'!B27,"")

This calendar will not have any input, it is there so everybody can go in
and see what the latest status of events are - it is rather a matrix of all
inputs from associates on their calendars combined into one overview, read
from a previous WS in this WB.
Would there be something else I could try?
THX
Driftwood

"Patrick Molloy" wrote:

Change this
Application.Goto rng, True
To
Cells(rng.row,1).Activate


"Driftwood" wrote in message
...

Hi, gurus of Excel
This is the code I am using:

Sub Go2Date()
Dim myFind As Integer
Dim rng As Range
myFind = ActiveSheet.Range("A2").Value
Set rng = ActiveSheet.Range( _
"$A$3:$G$564").Find(myFind, _
LookIn:=xlValues, LookAt:=xlWhole)
If Not rng Is Nothing Then
Application.Goto rng, True
Else
MsgBox myFind & " not found"
End If
End Sub

What I would like it to do:
Look at the date in A3
Then find that date in the range of the calendar (A3:G564)
When found, then go to that date.

If possible, I would rather want it to go to the beginning of that row
(column A) because:
Column A = Monday, Column 2 is Tuesday ....
Under each day, there are 10 cells, which read input from another
calendar
for scheduling events.
The idea is to click on the button I provided with assigned code, which
looks at the date the user wishes to see, then takes them to that date (
rather beginning of the week of that date col A) in the calendar.

Your help is greatly appreciated.
THX
Driftwood


--

Dave Peterson

What happened when you tried using Variant?



Driftwood wrote:

Hi Dave,
Thanks for the reply, but I had to leave in a hurry yesterday and can now
continue with my issue.I have implemented your change to my original code,
which now runs all the way through - to where it comes up "7/23/09 not found"
- I typed 7/23/09 in B2 - of course to trigger a response...
To your question, the value in the cell A2is this derived from this formula,
=IF(ISBLANK($B$2),$A$1,$B$2)
of which is A1= =TODAY()
and B2 is Blank - user can type in a date to search for if not "TODAY"(
which would become default).

Hope you still answer "OLD" replies...
Thanks
Driftwood

"Dave Peterson" wrote:

What's in that cell?

Dim myFind As Integer

Maybe the value in ActiveSheet.Range("A2") is too large for Integer types.

But I expect it to be that the value is "".

I'd use:

Dim myFind as Variant

Driftwood wrote:

Thanks,
I have implemented the change, but am receiving:
Run time error 6
Overflow

- which by the way I was receiving earlier and failed to mention - sorry
Is it perhaps because every cell in the range to be searched is filled with
a formula and not an actual value?
=IF('Daily log book'!B27<"",'Daily log book'!B27,"")

This calendar will not have any input, it is there so everybody can go in
and see what the latest status of events are - it is rather a matrix of all
inputs from associates on their calendars combined into one overview, read
from a previous WS in this WB.
Would there be something else I could try?
THX
Driftwood

"Patrick Molloy" wrote:

Change this
Application.Goto rng, True
To
Cells(rng.row,1).Activate


"Driftwood" wrote in message
...

Hi, gurus of Excel
This is the code I am using:

Sub Go2Date()
Dim myFind As Integer
Dim rng As Range
myFind = ActiveSheet.Range("A2").Value
Set rng = ActiveSheet.Range( _
"$A$3:$G$564").Find(myFind, _
LookIn:=xlValues, LookAt:=xlWhole)
If Not rng Is Nothing Then
Application.Goto rng, True
Else
MsgBox myFind & " not found"
End If
End Sub

What I would like it to do:
Look at the date in A3
Then find that date in the range of the calendar (A3:G564)
When found, then go to that date.

If possible, I would rather want it to go to the beginning of that row
(column A) because:
Column A = Monday, Column 2 is Tuesday ....
Under each day, there are 10 cells, which read input from another
calendar
for scheduling events.
The idea is to click on the button I provided with assigned code, which
looks at the date the user wishes to see, then takes them to that date (
rather beginning of the week of that date col A) in the calendar.

Your help is greatly appreciated.
THX
Driftwood


--

Dave Peterson


--

Dave Peterson

Sub drifter()
Dim A2 As Range, tablee As Range
Dim v As Variant
Set A2 = Range("A2")
Set tablee = Range("A3:G564")
v = A2.Value
For Each r In tablee
If r.Value = v Then
Cells(r.Row, "A").Select
Exit Sub
End If
Next
End Sub

--
Gary''s Student - gsnu200854


"Driftwood" wrote:


Hi, gurus of Excel
This is the code I am using:

Sub Go2Date()
Dim myFind As Integer
Dim rng As Range
myFind = ActiveSheet.Range("A2").Value
Set rng = ActiveSheet.Range( _
"$A$3:$G$564").Find(myFind, _
LookIn:=xlValues, LookAt:=xlWhole)
If Not rng Is Nothing Then
Application.Goto rng, True
Else
MsgBox myFind & " not found"
End If
End Sub

What I would like it to do:
Look at the date in A3
Then find that date in the range of the calendar (A3:G564)
When found, then go to that date.

If possible, I would rather want it to go to the beginning of that row
(column A) because:
Column A = Monday, Column 2 is Tuesday ....
Under each day, there are 10 cells, which read input from another calendar
for scheduling events.
The idea is to click on the button I provided with assigned code, which
looks at the date the user wishes to see, then takes them to that date (
rather beginning of the week of that date col A) in the calendar.

Your help is greatly appreciated.
THX
Driftwood

Student,
type mis match on:
If r.Value = v Then
I don't know if my previous reply would shed some light on how the sheet is
inteded to work ( as an information bas only - no input, so there are only
formula in eash cell)

Thanks
Driftwood.



"Gary''s Student" wrote:

Sub drifter()
Dim A2 As Range, tablee As Range
Dim v As Variant
Set A2 = Range("A2")
Set tablee = Range("A3:G564")
v = A2.Value
For Each r In tablee
If r.Value = v Then
Cells(r.Row, "A").Select
Exit Sub
End If
Next
End Sub

--
Gary''s Student - gsnu200854


"Driftwood" wrote:


Hi, gurus of Excel
This is the code I am using:

Sub Go2Date()
Dim myFind As Integer
Dim rng As Range
myFind = ActiveSheet.Range("A2").Value
Set rng = ActiveSheet.Range( _
"$A$3:$G$564").Find(myFind, _
LookIn:=xlValues, LookAt:=xlWhole)
If Not rng Is Nothing Then
Application.Goto rng, True
Else
MsgBox myFind & " not found"
End If
End Sub

What I would like it to do:
Look at the date in A3
Then find that date in the range of the calendar (A3:G564)
When found, then go to that date.

If possible, I would rather want it to go to the beginning of that row
(column A) because:
Column A = Monday, Column 2 is Tuesday ....
Under each day, there are 10 cells, which read input from another calendar
for scheduling events.
The idea is to click on the button I provided with assigned code, which
looks at the date the user wishes to see, then takes them to that date (
rather beginning of the week of that date col A) in the calendar.

Your help is greatly appreciated.
THX
Driftwood