Justin
asked on
Find and Replace Function not working in Excel
Hi Guys, I am trying to use the "Find and Replace" function in Excel on Column A of the attachment on the number 945518 and replace it with 945515.However, I get the message " We could not find what you're looking for". Any ideas why?
Mismapping.xlsx
Mismapping.xlsx
It is a derived value. You either need to change the value in column L or convert it to a literal value first
ASKER
I do not understand what are you looking at? In cell "A30" is the value 945518. I want to replace that value to 945515 by highlighting Column A and doing a Find and Replace.
Capture.JPG
Capture.JPG
ASKER
How can do a Find and Replace then?
ASKER
What if I do a Do Loop in a Macro in Column A. Would it find that Value then?
You either need to do a find and replace in column L or convert column A to values.
VBA should display value in one of these
.Text gives you a string representing what is displayed on the screen for the cell. Using .Text is usually a bad idea because you could get ####http://stackoverflow.com/questions/17359835/what-is-the-difference-between-text-value-and-value2
.Value2 gives you the underlying value of the cell (could be empty, string, error, number (double) or boolean)
.Value gives you the same as.Value2 except if the cell was formatted as currency or date it gives you a VBA currency (which may truncate decimal places) or VBA date.
Please try to run below VBA, then try to find and replace:
Sub ConvertTextToNumber()
Dim Ws As Worksheet
Dim LR As Long
Set Ws = ThisWorkbook.ActiveSheet 'Change As Needed
LR = Ws.Range("L" & Rows.Count).End(xlUp).Row 'Change As Needed
With Ws.Range("L2:L" & LR) 'Change As Needed
.NumberFormat = "General"
.Value = .Value
End With
End Sub
Doing just Find (rather than Find and Replace) you can set the Options to look at Values rather than formulas and it will find the value required.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Another approach is to run below code and enter each find value then replacement value:
Sub ChgInfo()
Dim WS As Worksheet
Dim Search As String
Dim Replacement As String
Dim Prompt As String
Dim Title As String
Dim MatchCase As Boolean
Set WS = ActiveSheet
Prompt = "What is the original value you want to replace?"
Title = "Search Value Input"
Search = InputBox(Prompt, Title)
Prompt = "What is the replacement value?"
Title = "Search Value Input"
Replacement = InputBox(Prompt, Title)
WS.Cells.Replace What:=Search, Replacement:=Replacement, _
LookAt:=xlPart, MatchCase:=False
End Sub
ASKER
superb
You're Welcome! Glad I was able to help