Change from Text to Value
 

Hello Steved

I would like to know please if it is possible to search for a value instead
off text.

I have over 700 entires and when I change "001" to text value it shows "1"

The below works as it should if I have cells as text


Sub Macro()

Dim varFound As Variant
Dim varSearch As Variant
Dim arrSearch As Variant
Dim strSearch As String

strSearch = InputBox("Type the string to be searched in Col C and D")
arrSearch = Split(strSearch & ",,", ",")

varSearch = arrSearch(0)

With Worksheets("Sheet1").Range("C:C")
Set varFound = .Find(varSearch, LookIn:=xlValues)
If Not varFound Is Nothing Then
firstAddress = varFound.Address
Do
If varFound.Offset(0, 1) = arrSearch(1) Then
varFound.Activate
If MsgBox(strSearch & " found at " & varFound.Address & _
vbLf & "Do you want to continue ?", vbYesNo) = vbNo Then Exit Sub
End If
Set varFound = .FindNext(varFound)
Loop While Not varFound Is Nothing And _
varFound.Address < firstAddress
End If
End With

End Sub

Thankyou.

Change from Text to Value
 

Hi Steved

To search for the value, use LookIn:=xlFormulas instead of
LookIn:=xlValues.

Mishell


"Steved" wrote in message
...
Hello Steved

I would like to know please if it is possible to search for a value
instead
off text.

I have over 700 entires and when I change "001" to text value it shows "1"

The below works as it should if I have cells as text


Sub Macro()

Dim varFound As Variant
Dim varSearch As Variant
Dim arrSearch As Variant
Dim strSearch As String

strSearch = InputBox("Type the string to be searched in Col C and D")
arrSearch = Split(strSearch & ",,", ",")

varSearch = arrSearch(0)

With Worksheets("Sheet1").Range("C:C")
Set varFound = .Find(varSearch, LookIn:=xlValues)
If Not varFound Is Nothing Then
firstAddress = varFound.Address
Do
If varFound.Offset(0, 1) = arrSearch(1) Then
varFound.Activate
If MsgBox(strSearch & " found at " & varFound.Address & _
vbLf & "Do you want to continue ?", vbYesNo) = vbNo Then Exit Sub
End If
Set varFound = .FindNext(varFound)
Loop While Not varFound Is Nothing And _
varFound.Address < firstAddress
End If
End With

End Sub

Thankyou.