Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query on small piece of code
I have the following very simple piece of code as part of a macro:
Selection.Find(What:="01/08/2002", _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False).Activate Quite simply, in a row that is selected, the instruction is to activate the cell where it finds the date shown. I have recorded this through simply "doing a Ctrl+F" with the recorder running, but when I run this macro to check it keeps failing. Any ideas on what the problem is as it's driving me mad, I can't see what's wrong. Thanks in advance Mike |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query on small piece of code
I should say that the error message was as follows:
Run-time error '91': Object variable or With block variable not set Thanks again for any help Mike "Mike" wrote in message ... I have the following very simple piece of code as part of a macro: Selection.Find(What:="01/08/2002", _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False).Activate Quite simply, in a row that is selected, the instruction is to activate the cell where it finds the date shown. I have recorded this through simply "doing a Ctrl+F" with the recorder running, but when I run this macro to check it keeps failing. Any ideas on what the problem is as it's driving me mad, I can't see what's wrong. Thanks in advance Mike |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query on small piece of code
Mike,
You'll get this error if the value is not found. Are you sure the value is in the range? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Mike" wrote in message ... I should say that the error message was as follows: Run-time error '91': Object variable or With block variable not set Thanks again for any help Mike "Mike" wrote in message ... I have the following very simple piece of code as part of a macro: Selection.Find(What:="01/08/2002", _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False).Activate Quite simply, in a row that is selected, the instruction is to activate the cell where it finds the date shown. I have recorded this through simply "doing a Ctrl+F" with the recorder running, but when I run this macro to check it keeps failing. Any ideas on what the problem is as it's driving me mad, I can't see what's wrong. Thanks in advance Mike |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query on small piece of code
Hi Mike
you probably have no cells selected when you invoke this macro which contains your search string. -- Regards Frank Kabel Frankfurt, Germany Mike wrote: I have the following very simple piece of code as part of a macro: Selection.Find(What:="01/08/2002", _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False).Activate Quite simply, in a row that is selected, the instruction is to activate the cell where it finds the date shown. I have recorded this through simply "doing a Ctrl+F" with the recorder running, but when I run this macro to check it keeps failing. Any ideas on what the problem is as it's driving me mad, I can't see what's wrong. Thanks in advance Mike |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query on small piece of code
Chip
Thanks. Having thought about the value itself, I have just run it again through the immediate window putting in the value "8/1/2002" and it now works fine. The problem is that the date is actually formatted as a UK and not American date. This seems to be the problem. This looks like it could cause problems for me, is there any way of reverting all settings to a UK setting, as it seems perhaps that with the visual basic it's running on a US format, even if Excel itself isn't? Regards Mike "Chip Pearson" wrote in message ... Mike, You'll get this error if the value is not found. Are you sure the value is in the range? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Mike" wrote in message ... I should say that the error message was as follows: Run-time error '91': Object variable or With block variable not set Thanks again for any help Mike "Mike" wrote in message ... I have the following very simple piece of code as part of a macro: Selection.Find(What:="01/08/2002", _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False).Activate Quite simply, in a row that is selected, the instruction is to activate the cell where it finds the date shown. I have recorded this through simply "doing a Ctrl+F" with the recorder running, but when I run this macro to check it keeps failing. Any ideas on what the problem is as it's driving me mad, I can't see what's wrong. Thanks in advance Mike |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query on small piece of code
set rng = Selection.Find(What:=clng(cDate("01/08/2002")), _
After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) if not rng is nothing then rng.Select Else msgbox "Not found" End if -- Regards, Tom Ogilvy "Mike" wrote in message ... I should say that the error message was as follows: Run-time error '91': Object variable or With block variable not set Thanks again for any help Mike "Mike" wrote in message ... I have the following very simple piece of code as part of a macro: Selection.Find(What:="01/08/2002", _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False).Activate Quite simply, in a row that is selected, the instruction is to activate the cell where it finds the date shown. I have recorded this through simply "doing a Ctrl+F" with the recorder running, but when I run this macro to check it keeps failing. Any ideas on what the problem is as it's driving me mad, I can't see what's wrong. Thanks in advance Mike |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query on small piece of code
cdate should respect your regional settings.
-- Regards, Tom Ogilvy "Mike" wrote in message ... Chip Thanks. Having thought about the value itself, I have just run it again through the immediate window putting in the value "8/1/2002" and it now works fine. The problem is that the date is actually formatted as a UK and not American date. This seems to be the problem. This looks like it could cause problems for me, is there any way of reverting all settings to a UK setting, as it seems perhaps that with the visual basic it's running on a US format, even if Excel itself isn't? Regards Mike "Chip Pearson" wrote in message ... Mike, You'll get this error if the value is not found. Are you sure the value is in the range? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Mike" wrote in message ... I should say that the error message was as follows: Run-time error '91': Object variable or With block variable not set Thanks again for any help Mike "Mike" wrote in message ... I have the following very simple piece of code as part of a macro: Selection.Find(What:="01/08/2002", _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False).Activate Quite simply, in a row that is selected, the instruction is to activate the cell where it finds the date shown. I have recorded this through simply "doing a Ctrl+F" with the recorder running, but when I run this macro to check it keeps failing. Any ideas on what the problem is as it's driving me mad, I can't see what's wrong. Thanks in advance Mike |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query on small piece of code
Cheers Tom and Chip - much appreciate the help
Mike "Tom Ogilvy" wrote in message ... set rng = Selection.Find(What:=clng(cDate("01/08/2002")), _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) if not rng is nothing then rng.Select Else msgbox "Not found" End if -- Regards, Tom Ogilvy "Mike" wrote in message ... I should say that the error message was as follows: Run-time error '91': Object variable or With block variable not set Thanks again for any help Mike "Mike" wrote in message ... I have the following very simple piece of code as part of a macro: Selection.Find(What:="01/08/2002", _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False).Activate Quite simply, in a row that is selected, the instruction is to activate the cell where it finds the date shown. I have recorded this through simply "doing a Ctrl+F" with the recorder running, but when I run this macro to check it keeps failing. Any ideas on what the problem is as it's driving me mad, I can't see what's wrong. Thanks in advance Mike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Small Query - Urgent | Excel Discussion (Misc queries) | |||
Small Query | Excel Discussion (Misc queries) | |||
Just A Small Code | Excel Discussion (Misc queries) | |||
Interpretation of a piece of code | Excel Discussion (Misc queries) | |||
What is wrong with this vba piece of code? | Excel Discussion (Misc queries) |