Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a variable
I am simply trying to find a user-input variable on the worksheet.
Dim itemn As String itemn = "&5050" Columns("a:a").Select Find(What:=itemn, After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False).Activate this code always returns the compiler error 'Object variable or With block not set.' This is the entire code. I do not recieve the error if use a set value in place of 'itemn'. I also do not recieve the error if I do not set 'itemn' to a value anyone able to tell me why this happens??? thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a variable
Hi Ben
This is working for me with &5050 Sub Find_First() Dim FindString As String Dim Rng As Range FindString = InputBox("Enter a Search value") If Trim(FindString) < "" Then Set Rng = Range("A:A").Find(What:=FindString, _ After:=Range("A" & Rows.Count), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not Rng Is Nothing Then Application.Goto Rng, True End If End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "ben" wrote in message ... I am simply trying to find a user-input variable on the worksheet. Dim itemn As String itemn = "&5050" Columns("a:a").Select Find(What:=itemn, After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False).Activate this code always returns the compiler error 'Object variable or With block not set.' This is the entire code. I do not recieve the error if use a set value in place of 'itemn'. I also do not recieve the error if I do not set 'itemn' to a value anyone able to tell me why this happens??? thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a variable
umm, no that still does not quite work, It seems that without the special
character, '5050' instead of '&5050' it will find the cells everytime, if I put any special character onto it, I receive the '91' Run time error everytime Do the special characters perhaps change how VBA reads it? "Ron de Bruin" wrote: Hi Ben This is working for me with &5050 Sub Find_First() Dim FindString As String Dim Rng As Range FindString = InputBox("Enter a Search value") If Trim(FindString) < "" Then Set Rng = Range("A:A").Find(What:=FindString, _ After:=Range("A" & Rows.Count), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not Rng Is Nothing Then Application.Goto Rng, True End If End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "ben" wrote in message ... I am simply trying to find a user-input variable on the worksheet. Dim itemn As String itemn = "&5050" Columns("a:a").Select Find(What:=itemn, After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False).Activate this code always returns the compiler error 'Object variable or With block not set.' This is the entire code. I do not recieve the error if use a set value in place of 'itemn'. I also do not recieve the error if I do not set 'itemn' to a value anyone able to tell me why this happens??? thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a variable
Sub Tester1()
Dim itemn As String itemn = "&5050" Columns("A:A").Select Selection.Find(What:=itemn, After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False).Activate Debug.Print itemn End Sub worked fine for me. -- Regards, Tom Ogilvy "ben" wrote in message ... umm, no that still does not quite work, It seems that without the special character, '5050' instead of '&5050' it will find the cells everytime, if I put any special character onto it, I receive the '91' Run time error everytime Do the special characters perhaps change how VBA reads it? "Ron de Bruin" wrote: Hi Ben This is working for me with &5050 Sub Find_First() Dim FindString As String Dim Rng As Range FindString = InputBox("Enter a Search value") If Trim(FindString) < "" Then Set Rng = Range("A:A").Find(What:=FindString, _ After:=Range("A" & Rows.Count), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not Rng Is Nothing Then Application.Goto Rng, True End If End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "ben" wrote in message ... I am simply trying to find a user-input variable on the worksheet. Dim itemn As String itemn = "&5050" Columns("a:a").Select Find(What:=itemn, After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False).Activate this code always returns the compiler error 'Object variable or With block not set.' This is the entire code. I do not recieve the error if use a set value in place of 'itemn'. I also do not recieve the error if I do not set 'itemn' to a value anyone able to tell me why this happens??? thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a variable
Ben-
Your code seems to works fine as long as the five character string "&5050" exists in column A. You get the 'Object variable or With block not set' error when Find does not return any values. VBA generates the error because there is no cell referece for it to activate. You shold get the same error when "itemn" is set to any string that does not exist in Column A. The "Find" is set to look only at the whole string. If you want to find the substring "&5050" in one of the cells, you should set Look At value. to "LookAt:=xlPart" Stan Shoemaker Palo Alto, CA "ben" wrote: I am simply trying to find a user-input variable on the worksheet. Dim itemn As String itemn = "&5050" Columns("a:a").Select Find(What:=itemn, After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False).Activate this code always returns the compiler error 'Object variable or With block not set.' This is the entire code. I do not recieve the error if use a set value in place of 'itemn'. I also do not recieve the error if I do not set 'itemn' to a value anyone able to tell me why this happens??? thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a variable
The exact variable '&5050' does exist in column A . even expanding the
selection to search in all the cells seems to not work. Neither does just searching for part. "stanshoe" wrote: Ben- Your code seems to works fine as long as the five character string "&5050" exists in column A. You get the 'Object variable or With block not set' error when Find does not return any values. VBA generates the error because there is no cell referece for it to activate. You shold get the same error when "itemn" is set to any string that does not exist in Column A. The "Find" is set to look only at the whole string. If you want to find the substring "&5050" in one of the cells, you should set Look At value. to "LookAt:=xlPart" Stan Shoemaker Palo Alto, CA "ben" wrote: I am simply trying to find a user-input variable on the worksheet. Dim itemn As String itemn = "&5050" Columns("a:a").Select Find(What:=itemn, After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False).Activate this code always returns the compiler error 'Object variable or With block not set.' This is the entire code. I do not recieve the error if use a set value in place of 'itemn'. I also do not recieve the error if I do not set 'itemn' to a value anyone able to tell me why this happens??? thanks |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a variable
ok guys i solved the issue, sorry for the trouble, it seems that the cells i
had the data in was merged with other cells, and VBA did not like that very much, once i took that into account I am able to run the code with no issue "stanshoe" wrote: Ben- Your code seems to works fine as long as the five character string "&5050" exists in column A. You get the 'Object variable or With block not set' error when Find does not return any values. VBA generates the error because there is no cell referece for it to activate. You shold get the same error when "itemn" is set to any string that does not exist in Column A. The "Find" is set to look only at the whole string. If you want to find the substring "&5050" in one of the cells, you should set Look At value. to "LookAt:=xlPart" Stan Shoemaker Palo Alto, CA "ben" wrote: I am simply trying to find a user-input variable on the worksheet. Dim itemn As String itemn = "&5050" Columns("a:a").Select Find(What:=itemn, After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False).Activate this code always returns the compiler error 'Object variable or With block not set.' This is the entire code. I do not recieve the error if use a set value in place of 'itemn'. I also do not recieve the error if I do not set 'itemn' to a value anyone able to tell me why this happens??? thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable Find | Excel Discussion (Misc queries) | |||
Find Max from Variable Range | Excel Worksheet Functions | |||
Cells.Find with a variable | Excel Discussion (Misc queries) | |||
Right Text - Variable Find | Excel Discussion (Misc queries) | |||
Cells.Find error Object variable or With block variable not set | Excel Programming |