Link to home
Start Free TrialLog in
Avatar of Justin
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
Avatar of Shaun Vermaak
Shaun Vermaak
Flag of Australia image

It is a derived value. You either need to change the value in column L or convert it to a literal value first
 User generated image
Avatar of Justin
Justin

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
No in A30 is
=LEFT(L30,6)

Open in new window

User generated image
Avatar of Justin

ASKER

How can do a Find and Replace then?
Avatar of Justin

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 ####


.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.
http://stackoverflow.com/questions/17359835/what-is-the-difference-between-text-value-and-value2
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

Open in new window

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
Avatar of Shums Faruk
Shums Faruk
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

Open in new window

Avatar of Justin

ASKER

superb
You're Welcome! Glad I was able to help