Find using a variable value

[S Shipley]

I trying to create a small macro to look up a value typed in a textbox using
the following code. If I try to set the variable S to the value of TextBox1
the find does not return anything. If I set S to the actual value then the
find is successful.

Private Sub CommandButton1_Click()
Dim strRange As String
Dim strMyString As String
Dim rngFound As Range
MyString = TextBox1.Value
Set rngFound = Sheets("Dealership Report").Cells.Find(MyString, , ,
xlPart)

' TextBox1.Value = 240599 , actual value entered in text box
S = TextBox1.Value
Set SearchRange = Worksheets("sheets 1").Range("A1:A3671")
Set c = SearchRange.Find(What:=S, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If c Is Nothing Then
MsgBox "Not found"
Else
strRange = c.Address
Range(strRange).Activate
MsgBox "Found"
End If
End Sub

 

[JLGWhiz]

Is it giving you a type mismatch error or just not returning anything?

 

[S Shipley]

It's just not finding anything.

Sam

Is it giving you a type mismatch error or just not returning anything?

 

[JLGWhiz]

I was wondering if you were trying to find an integer with text. If your
data types are not the same, it will not return anything although it looks
like the data is there.
The text box value is text, so you need to check the values in you search
range to be sure they are text or maybe change the variable type with S =
CLng(S) to make it a number. I'm just guessing, since I don't see anything
wrong with the code, other than the sheet name "Sheets 1". If that was not a
good name you would get a "Subscript out of range message".

 

[S Shipley]

I tried changing S using Clng and get the same results. When I run the code
I get no errors it just displays the Not Found message. When I hard code the
value to find (240599 for example) it finds the value and executes the code
after the Else statement.

If I go to the cell that contains the value 240599 and change it to '240599
the code finds the value so now I'm baffled as to why it can't find a number.
I have tried declaring S as text, a long, double, and as a variant and get
the same results. I have also tried setting all the values in this column to
text but that doesn't work either.

Sam

 

[JLGWhiz]

I think I found the problem. I set up a textbox on a sheet and used this
syntax:

textbox1.value = 4
a = textbox1.value
msgbox a

I got a blank. But when I changed the syntax to:

Sheets(1).TextBox1.Value = 4
a = TextBox1.Value
MsgBox a

I got the 4. So apparently you need ot qualify your textbox with either the
sheet or the UserForm, whichever it is on.

 

[JLGWhiz]

The correct code should be:

Sheets(1).TextBox1.Value = 4
a = Sheets(1).TextBox1.Value
MsgBox a

 

[Abhishake Saravgi]

Hi hope this Code help you
Select the Range in which you want to find your text through Code .It has
also taken care of if it does not find anything in the below If Else Block


tofind= "the string you want to find"
This workbook.worksheets("Sheet1").Activate
This workbook.worksheets("Sheet1").range("A122").select

Set FoundText = Selection.Find(What:=tofind, After:=ActiveCell, LookIn _
:=xlvalues, LookAt:=xlwhole, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False)


If Not FoundText Is Nothing Then
FoundText.Select
FoundCellRow = ActiveCell.Row
Else
End If

 

[S Shipley]

Thanks JLGWhiz - this code solved the problem. I am now finding the values.

Sam