Look at, Find, then go to

[Driftwood]

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

 

[Patrick Molloy]

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

 

[Gary''s Student]

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

 

[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

 

[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.

 

[Dave Peterson]

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]

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]

What happened when you tried using Variant?

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

 

[Driftwood]

Thanks for replying...
That was the change I implemented in the original code, which now runs all
the way through, without breaks, BUT: it states: "7/23/09 not found" which is
typed into a cell ( explained in the prevouis reply).

Driftwood

 

[Driftwood]

I have changed my date formatting from "Date" to "General"
and the code works GREAT!
Which is weird, because cell A2 has always had the same format as the cells
in the searched range.
there must be something with the "date" format it does not like, because the
original code works in another worksheet, and yes, the dates are all
formatted as "General" not as "Date"...
I do want to keep my calendar formatted with dates that make sense to us
normal humnas, so
I will rearange my WS, so that I have all dates in column H in "General"
format lined up with the rows they fall into, then have the code search for
A2 in col H and go to col A when found.

Dave and Gary's Student
Thank you for your patience and great advice and suggestions.
Driftwood

Thanks for replying...
That was the change I implemented in the original code, which now runs all
the way through, without breaks, BUT: it states: "7/23/09 not found" which is
typed into a cell ( explained in the prevouis reply).

Driftwood

 

[Dave Peterson]

Dates are strange.

If you know you're looking for a date, you can sometimes use:

.cells.find what:=clng(somedatehere), ...

Or use application.index() and look through each column (or row) one at a time.

Thanks for replying...
That was the change I implemented in the original code, which now runs all
the way through, without breaks, BUT: it states: "7/23/09 not found" which is
typed into a cell ( explained in the prevouis reply).

Driftwood