Why Does Find and Replace Not work on a Table

[JeffGrant]

Hi All,

I would like to understand, why I cant solve this one.

I have this code

Sub BMFnR()

Dim Rng1, Rng2 As Range
Dim FndList, x&
Dim lRow As Double

Sheet8.Cells(1, 1).Select
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    
'Data Range to look in
Set Rng1 = Sheet8.Range("G1:G" & lRow)

'Data Range to Replace
Set Rng2 = Sheet1.Range("D27").CurrentRegion

FndList = Rng2.Range("D27").CurrentRegion
For x = 1 To UBound(FndList)
    Rng1.Cells.Replace What:=FndList(x, 4), Replacement:=FndList(x, 5), LookAt:=xlPart
Next

Sheet1.Range("A1").Select
End Sub

which works great when doing a multi value Find and Replace on Ranges. However, I would like to change the input data to a table.

I don't understand why the same code does not work on a Table. I keep getting Type Mismatch error on this line For x = 1 To UBound(FndList)

I appreciate that this can easily be done with PQ, but I am not that familiar with PQ to do this. I would like to keep it all in VBA

Thanks for your help

 

[RoryA]

What are you aiming for with this line:

FndList = Rng2.Range("D27").CurrentRegion

 

[JeffGrant]

Hi Rory, I got it to work. Thanks.

Just bythe way, what that line does is set up the range of the values to find and the values to replace.

 

[RoryA]

I know what you were trying to do, it was just odd syntax. Why do you need to offset 26 rows and 3 columns from the top of rng2 and then get the currentregion?

 

[JeffGrant]

Because buddy, I am a novice programmer and don't posess the skills of good folk like yourself.

Can I,

delete this line
FndList = Rng2.Range("D27").CurrentRegion

and change the loop to this ?

For x = 1 To UBound(FndList)
Rng1.Cells.Replace What:=Rng2(x, 4), Replacement:=Rng2(x, 5), LookAt:=xlPart
Next

because I have not tried that