Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change the text in the legend of a chart w/o changing pivot text | Charts and Charting in Excel | |||
Copy text cell & change text color conditionally | Excel Programming | |||
How can I change text to proper text in multiple cells. | Excel Discussion (Misc queries) | |||
Change of text or background color doesn't change on the screen. | Excel Discussion (Misc queries) | |||
HOW TO CHANGE BAHTTEXT (THAI TEXT) TO ENGLISH TEXT IN EXCEL | Excel Worksheet Functions |