Limiting Find and Replace to first X characters

[BrandynBlaze]

Hi all,

I have a program that performs a search that looks for a 4 digit account number within an 8 digit item number and if it matches the number it changes to the format to the formatting on my list (Which reflects what actions need to be taken for given account).

The search is extremely basic and works currently, but I'd like it to only consider the first 4 characters so that internal matches don't flag the item.

For instance account # is 6011:

I want it to match 60117806, but not 54601185.

I've tried using Left(current cell, 4) to limit the characters searched, but it's still returning internal matches for some reason...
All the other attempts I've made have prevented the program from executing fully, and I've spent all my aloted time to play around with it, so I'm hoping for some help.

Sheet1.Cells.Replace _

        What:=cell.Value Replacement:=cell.Value, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=True

 

[VorLag]

Have you tried something like

If Left(cell.Value,4) = 6011 Then

to limit your find and replace to only the cells that begin with whatever string you're looking for?

 

[JoeMo]

Assuming the two test values you posted are in A1:A2,

Sub Test()
rpl = 6011
With Range("A1:A2")
    .Replace what:=rpl & "*", replacement:=rpl, lookat:=xlWhole
End With
End Sub

 

[BrandynBlaze]

Okay, so can it not be done by modifying the built in find and replace function? I've got some additional code that I'd rather not move around if I don't have to. I'm pretty new to VBA and my code is fragile, to say the least. I was hoping it could just been done by limiting the "What" portion of the built in function. I can't get the left(cell.value,4) to work in that function, and because my search consists of a range of search terms that are variable I can't set the value for it to be looking for.

When I tried using the wildcard in the search it just deletes all the characters after the match.

I've included the full code for my search function, to see if there is an easy place to edit. Or rewrite the whole I guess, I'm sure it could be done a much better way. Maybe I'm just daft and the right answer has already been presented but if it was I didn't get it

Sub FindAndReplace(Tofind) 'Tofind is the range of search terms that need to be cycled through containing each account #
      
    Dim cell As Range
    
        For Each cell In Tofind
            If Not Tofind.Cells.Find(cell.Value, , xlValues, xlPart, , , True) Is Nothing Then 'Double negative, if there is a value in ToFind then...
                
                With Application.ReplaceFormat.Font
                    .Color = ActiveCell.Font.Color
                End With
                
                With Application.ReplaceFormat.Interior
                    .Color = ActiveCell.Interior.Color
                End With
                
                Sheet1.Cells.Replace _
                What:=cell.Value, Replacement:=cell.Value, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=True
       
            End If
        Next cell
       
End Sub

 

[pgc01]

Hi

Joe posted a solution.

Did you try it?
Did it work?