JeffGrant
Well-known Member
- Joined
- Apr 7, 2021
- Messages
- 519
- Office Version
- 365
- Platform
- Windows
Hi All,
I would like to understand, why I cant solve this one.
I have this code
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
I would like to understand, why I cant solve this one.
I have this code
VBA 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