Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look at, Find, then go to
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look at, Find, then go to
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look at, Find, then go to
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look at, Find, then go to
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look at, Find, then go to
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look at, Find, then go to
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look at, Find, then go to
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look at, Find, then go to
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look at, Find, then go to
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" wrote: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look at, Find, then go to
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 "Driftwood" wrote: 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" wrote: 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look at, Find, then go to
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. Driftwood wrote: 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" wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find rows with a common item and find or highlight difference | Excel Programming | |||
Find and Replace - delete the remainder of the text in the cell after my Find | Excel Programming | |||
find and delete duplicate entries in two columns or find and prin. | Excel Programming | |||
find and delete text, find a 10-digit number and put it in a textbox | Excel Programming | |||
backwards find function to find character in a string of text | Excel Programming |