Find a variable

[Guest]

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

 

[Ron de Bruin]

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

 

[Guest]

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?

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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

 

[Tom Ogilvy]

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.