Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cells.Find: Why can't I pass a variable?
In the code below I am not able to pass the variable "sFindMe" with out an
error, typical "object variable or with block variable not set" If I serach for string ".0625" it crashes, if I search for string "1" it runs through all sheets fine? What am I doing wrong besides programming in VBA? I just realized the code wont stop at each instance for me to view LOL ================================ Sub CommandButton3_Click() Dim ws As Worksheet Dim sFindMe As String sFindMe = TextBox1.Value For Each ws In ActiveWorkbook.Worksheets ws.Select Cells.Find(What:=sFindMe, After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate Next End Sub ==================================== -- Regards Rick XP Pro Office 2007 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cells.Find: Why can't I pass a variable?
hi
if something is not set then you need to set it. dim rng as range set rng = Cells.Find(What:=sFindMe, _ After:=ActiveCell, _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False).Activate any time you then the "...not set" error, this is usually it. Regards FSt1 "Rick S." wrote: In the code below I am not able to pass the variable "sFindMe" with out an error, typical "object variable or with block variable not set" If I serach for string ".0625" it crashes, if I search for string "1" it runs through all sheets fine? What am I doing wrong besides programming in VBA? I just realized the code wont stop at each instance for me to view LOL ================================ Sub CommandButton3_Click() Dim ws As Worksheet Dim sFindMe As String sFindMe = TextBox1.Value For Each ws In ActiveWorkbook.Worksheets ws.Select Cells.Find(What:=sFindMe, After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate Next End Sub ==================================== -- Regards Rick XP Pro Office 2007 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cells.Find: Why can't I pass a variable?
I think it makes the code easier to understand if you try the .Find and then
look to see if it was successful: Dim FoundCell as Range dim ws as worksheet .... for each ws in activeworkbook.worksheets with ws Set foundcell = .cells.find(what:=sFindMe, _ After:=.cells(.cells.count), _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) if foundcell is nothing then 'not found, what should happen else 'it was found, what should happen application.goto foundcell, scroll:=true exit for '??? end if end with next ws (Uncompiled, untested. Watch for typos.) Rick S. wrote: In the code below I am not able to pass the variable "sFindMe" with out an error, typical "object variable or with block variable not set" If I serach for string ".0625" it crashes, if I search for string "1" it runs through all sheets fine? What am I doing wrong besides programming in VBA? I just realized the code wont stop at each instance for me to view LOL ================================ Sub CommandButton3_Click() Dim ws As Worksheet Dim sFindMe As String sFindMe = TextBox1.Value For Each ws In ActiveWorkbook.Worksheets ws.Select Cells.Find(What:=sFindMe, After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate Next End Sub ==================================== -- Regards Rick XP Pro Office 2007 -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cells.Find: Why can't I pass a variable?
FSt1 - You need to drop the .Activate from the end of the statement.
The find method of a range returns a range object if one is found. If one is not found then you are going to generate an error at the .activate line. So as FSt1 indicates you are best off to set a range object to the return value of the Find command. Then test the object to see if it really exists. Basically your code is trying to fry up a fish that you may not have caught. The final issue is that you probably are not searching the sheet that you think you are. Even though you select ws if this code is contained in a worksheet then by default it will check itself unless explicitly told otherwise. Try something more like this... '================================ Sub CommandButton3_Click() Dim ws As Worksheet Dim sFindMe As String Dim rngFound As Range sFindMe = TextBox1.Value For Each ws In ActiveWorkbook.Worksheets Set rngFound = ws.Cells.Find(What:=sFindMe, _ LookIn:=xlValues, _ LookAt:=xlPart, _ MatchCase:=False) If Not rngFound Is Nothing Then ws.Select rngFound.Select Exit For End If Next ws End Sub '==================================== -- HTH... Jim Thomlinson "FSt1" wrote: hi if something is not set then you need to set it. dim rng as range set rng = Cells.Find(What:=sFindMe, _ After:=ActiveCell, _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False).Activate any time you then the "...not set" error, this is usually it. Regards FSt1 "Rick S." wrote: In the code below I am not able to pass the variable "sFindMe" with out an error, typical "object variable or with block variable not set" If I serach for string ".0625" it crashes, if I search for string "1" it runs through all sheets fine? What am I doing wrong besides programming in VBA? I just realized the code wont stop at each instance for me to view LOL ================================ Sub CommandButton3_Click() Dim ws As Worksheet Dim sFindMe As String sFindMe = TextBox1.Value For Each ws In ActiveWorkbook.Worksheets ws.Select Cells.Find(What:=sFindMe, After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate Next End Sub ==================================== -- Regards Rick XP Pro Office 2007 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cells.Find: Why can't I pass a variable?
jim,
you're right. I just copied his code without looking at it closely. I was just trying to make his error go away. thanks for the correction and the additional input. regards FSt1 "Jim Thomlinson" wrote: FSt1 - You need to drop the .Activate from the end of the statement. The find method of a range returns a range object if one is found. If one is not found then you are going to generate an error at the .activate line. So as FSt1 indicates you are best off to set a range object to the return value of the Find command. Then test the object to see if it really exists. Basically your code is trying to fry up a fish that you may not have caught. The final issue is that you probably are not searching the sheet that you think you are. Even though you select ws if this code is contained in a worksheet then by default it will check itself unless explicitly told otherwise. Try something more like this... '================================ Sub CommandButton3_Click() Dim ws As Worksheet Dim sFindMe As String Dim rngFound As Range sFindMe = TextBox1.Value For Each ws In ActiveWorkbook.Worksheets Set rngFound = ws.Cells.Find(What:=sFindMe, _ LookIn:=xlValues, _ LookAt:=xlPart, _ MatchCase:=False) If Not rngFound Is Nothing Then ws.Select rngFound.Select Exit For End If Next ws End Sub '==================================== -- HTH... Jim Thomlinson "FSt1" wrote: hi if something is not set then you need to set it. dim rng as range set rng = Cells.Find(What:=sFindMe, _ After:=ActiveCell, _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False).Activate any time you then the "...not set" error, this is usually it. Regards FSt1 "Rick S." wrote: In the code below I am not able to pass the variable "sFindMe" with out an error, typical "object variable or with block variable not set" If I serach for string ".0625" it crashes, if I search for string "1" it runs through all sheets fine? What am I doing wrong besides programming in VBA? I just realized the code wont stop at each instance for me to view LOL ================================ Sub CommandButton3_Click() Dim ws As Worksheet Dim sFindMe As String sFindMe = TextBox1.Value For Each ws In ActiveWorkbook.Worksheets ws.Select Cells.Find(What:=sFindMe, After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate Next End Sub ==================================== -- Regards Rick XP Pro Office 2007 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cells.Find: Why can't I pass a variable?
Thanks to all!
I now understand why I always see code examples Like this: dim rng as range set rng = Cells.Find(What:=sFindMe I did not realize this statement is key: "if something is not set then you need to set it" This was a really good topic!!! -- Regards Rick XP Pro Office 2007 "Rick S." wrote: In the code below I am not able to pass the variable "sFindMe" with out an error, typical "object variable or with block variable not set" If I serach for string ".0625" it crashes, if I search for string "1" it runs through all sheets fine? What am I doing wrong besides programming in VBA? I just realized the code wont stop at each instance for me to view LOL ================================ Sub CommandButton3_Click() Dim ws As Worksheet Dim sFindMe As String sFindMe = TextBox1.Value For Each ws In ActiveWorkbook.Worksheets ws.Select Cells.Find(What:=sFindMe, After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate Next End Sub ==================================== -- Regards Rick XP Pro Office 2007 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cells.Find: Why can't I pass a variable?
This is maddening, maddening I tell you!
At first the code worked fine, now it doesnt? The Find Dialog box will not open!? As long as the Find dialog box is not open it will not iterate thru worksheets. Code tested in native form from Jim Thomlinson's reply. Here is my complete code: (still testing and writing) ===================Activate User Form Sub FindIt() SearchWorkBook.Show End Sub ===================Do some work Sub CommandButton1_Click() Dim ws As Worksheet Dim sFindMe As String Dim rngFound As Range sFindMe = TextBox1.Value For Each ws In ActiveWorkbook.Worksheets Set rngFound = ws.Cells.Find(What:=sFindMe, _ LookIn:=xlValues, _ LookAt:=xlPart, _ MatchCase:=False) If Not rngFound Is Nothing Then ws.Select rngFound.Select Exit For End If Next ws End Sub Sub CommandButton2_Click() Unload Me End Sub ==================== Did I change something I don't see? I was adding code to report found data and Excel crashed, since then the macro quit working as it only finds a single instance of the search text on a single sheet. Continued clicking of the Command1 button (OK) does nothing more than what appears to refresh the screen. -- Regards Rick XP Pro Office 2007 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cells.Find: Why can't I pass a variable?
Each time you click the button, excel starts the routine from scratch. So it's
always going to find that same cell as the first occurance. I'm not sure what Find dialog box you're writing about, though. You may want to try Jan Karel Pieterse's FlexFind to see how he approached it: http://www.oaltd.co.uk/MVP/ ps. I'd still specify all the .find arguments. These arguments are shared between the code and the user interface. And you may be relying on an argument that really isn't what you expected. Rick S. wrote: This is maddening, maddening I tell you! At first the code worked fine, now it doesnt? The Find Dialog box will not open!? As long as the Find dialog box is not open it will not iterate thru worksheets. Code tested in native form from Jim Thomlinson's reply. Here is my complete code: (still testing and writing) ===================Activate User Form Sub FindIt() SearchWorkBook.Show End Sub ===================Do some work Sub CommandButton1_Click() Dim ws As Worksheet Dim sFindMe As String Dim rngFound As Range sFindMe = TextBox1.Value For Each ws In ActiveWorkbook.Worksheets Set rngFound = ws.Cells.Find(What:=sFindMe, _ LookIn:=xlValues, _ LookAt:=xlPart, _ MatchCase:=False) If Not rngFound Is Nothing Then ws.Select rngFound.Select Exit For End If Next ws End Sub Sub CommandButton2_Click() Unload Me End Sub ==================== Did I change something I don't see? I was adding code to report found data and Excel crashed, since then the macro quit working as it only finds a single instance of the search text on a single sheet. Continued clicking of the Command1 button (OK) does nothing more than what appears to refresh the screen. -- Regards Rick XP Pro Office 2007 -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cells.Find: Why can't I pass a variable?
That addin is exactly what I was attempting to do in my project! I searched
many words looking for possible examples or code to use and just did not find FlexFind, nor did I know to search for "flex". Thanks for the link to the addin Dave!!! And thanks to Jan Karel Pieterse's "FlexFind" for saving my sanity! -- Regards Rick XP Pro Office 2007 "Dave Peterson" wrote: Each time you click the button, excel starts the routine from scratch. So it's always going to find that same cell as the first occurance. I'm not sure what Find dialog box you're writing about, though. You may want to try Jan Karel Pieterse's FlexFind to see how he approached it: http://www.oaltd.co.uk/MVP/ ps. I'd still specify all the .find arguments. These arguments are shared between the code and the user interface. And you may be relying on an argument that really isn't what you expected. Rick S. wrote: This is maddening, maddening I tell you! At first the code worked fine, now it doesn€„¢t? The Find Dialog box will not open!? As long as the Find dialog box is not open it will not iterate thru worksheets. Code tested in native form from Jim Thomlinson's reply. Here is my complete code: (still testing and writing) ===================Activate User Form Sub FindIt() SearchWorkBook.Show End Sub ===================Do some work Sub CommandButton1_Click() Dim ws As Worksheet Dim sFindMe As String Dim rngFound As Range sFindMe = TextBox1.Value For Each ws In ActiveWorkbook.Worksheets Set rngFound = ws.Cells.Find(What:=sFindMe, _ LookIn:=xlValues, _ LookAt:=xlPart, _ MatchCase:=False) If Not rngFound Is Nothing Then ws.Select rngFound.Select Exit For End If Next ws End Sub Sub CommandButton2_Click() Unload Me End Sub ==================== Did I change something I don't see? I was adding code to report found data and Excel crashed, since then the macro quit working as it only finds a single instance of the search text on a single sheet. Continued clicking of the Command1 button (OK) does nothing more than what appears to refresh the screen. -- Regards Rick XP Pro Office 2007 -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cells.Find: Why can't I pass a variable?
Jan Karel's name manager is also great to use, much better than the one in
Excel 2007 -- Regards, Peo Sjoblom "Rick S." wrote in message ... That addin is exactly what I was attempting to do in my project! I searched many words looking for possible examples or code to use and just did not find FlexFind, nor did I know to search for "flex". Thanks for the link to the addin Dave!!! And thanks to Jan Karel Pieterse's "FlexFind" for saving my sanity! -- Regards Rick XP Pro Office 2007 "Dave Peterson" wrote: Each time you click the button, excel starts the routine from scratch. So it's always going to find that same cell as the first occurance. I'm not sure what Find dialog box you're writing about, though. You may want to try Jan Karel Pieterse's FlexFind to see how he approached it: http://www.oaltd.co.uk/MVP/ ps. I'd still specify all the .find arguments. These arguments are shared between the code and the user interface. And you may be relying on an argument that really isn't what you expected. Rick S. wrote: This is maddening, maddening I tell you! At first the code worked fine, now it doesn?Tt? The Find Dialog box will not open!? As long as the Find dialog box is not open it will not iterate thru worksheets. Code tested in native form from Jim Thomlinson's reply. Here is my complete code: (still testing and writing) ===================Activate User Form Sub FindIt() SearchWorkBook.Show End Sub ===================Do some work Sub CommandButton1_Click() Dim ws As Worksheet Dim sFindMe As String Dim rngFound As Range sFindMe = TextBox1.Value For Each ws In ActiveWorkbook.Worksheets Set rngFound = ws.Cells.Find(What:=sFindMe, _ LookIn:=xlValues, _ LookAt:=xlPart, _ MatchCase:=False) If Not rngFound Is Nothing Then ws.Select rngFound.Select Exit For End If Next ws End Sub Sub CommandButton2_Click() Unload Me End Sub ==================== Did I change something I don't see? I was adding code to report found data and Excel crashed, since then the macro quit working as it only finds a single instance of the search text on a single sheet. Continued clicking of the Command1 button (OK) does nothing more than what appears to refresh the screen. -- Regards Rick XP Pro Office 2007 -- Dave Peterson |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cells.Find: Why can't I pass a variable?
Yes, I am looking that one over for possible future use. ;)
-- Regards Rick XP Pro Office 2007 "Peo Sjoblom" wrote: Jan Karel's name manager is also great to use, much better than the one in Excel 2007 -- Regards, Peo Sjoblom "Rick S." wrote in message ... That addin is exactly what I was attempting to do in my project! I searched many words looking for possible examples or code to use and just did not find FlexFind, nor did I know to search for "flex". Thanks for the link to the addin Dave!!! And thanks to Jan Karel Pieterse's "FlexFind" for saving my sanity! -- Regards Rick XP Pro Office 2007 "Dave Peterson" wrote: Each time you click the button, excel starts the routine from scratch. So it's always going to find that same cell as the first occurance. I'm not sure what Find dialog box you're writing about, though. You may want to try Jan Karel Pieterse's FlexFind to see how he approached it: http://www.oaltd.co.uk/MVP/ ps. I'd still specify all the .find arguments. These arguments are shared between the code and the user interface. And you may be relying on an argument that really isn't what you expected. Rick S. wrote: This is maddening, maddening I tell you! At first the code worked fine, now it doesnâ?Tt? The Find Dialog box will not open!? As long as the Find dialog box is not open it will not iterate thru worksheets. Code tested in native form from Jim Thomlinson's reply. Here is my complete code: (still testing and writing) ===================Activate User Form Sub FindIt() SearchWorkBook.Show End Sub ===================Do some work Sub CommandButton1_Click() Dim ws As Worksheet Dim sFindMe As String Dim rngFound As Range sFindMe = TextBox1.Value For Each ws In ActiveWorkbook.Worksheets Set rngFound = ws.Cells.Find(What:=sFindMe, _ LookIn:=xlValues, _ LookAt:=xlPart, _ MatchCase:=False) If Not rngFound Is Nothing Then ws.Select rngFound.Select Exit For End If Next ws End Sub Sub CommandButton2_Click() Unload Me End Sub ==================== Did I change something I don't see? I was adding code to report found data and Excel crashed, since then the macro quit working as it only finds a single instance of the search text on a single sheet. Continued clicking of the Command1 button (OK) does nothing more than what appears to refresh the screen. -- Regards Rick XP Pro Office 2007 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pass variable to NORMINSV function to get only mean value | Excel Worksheet Functions | |||
How to pass a variable into an SQL statement | Excel Discussion (Misc queries) | |||
How do I my find pass word in excel | Setting up and Configuration of Excel | |||
Sum cells based on a row variable and seperate column variable | Excel Worksheet Functions | |||
How to (re)set a range.value to pass -0- to a "double" variable | Excel Discussion (Misc queries) |