Rita F
New Member
- Joined
- Jan 27, 2021
- Messages
- 8
- Office Version
- 365
- Platform
- Windows
I try to write the script with macro for Vlookup between 2 listObjects(dynamic tables) in 2 different sheets (with iferror). I tried the code below that works with regular range, but I want it more dynamical with references to the list-objects:
*********Dim rg As Range
With ActiveSheet
Set rg = .Range("B2") 'Put formulas in cells until end of data in column A
Set rg = Range(rg, .Cells(.Rows.Count, rg.Column).End(xlUp))
rg.Offset(0, 8).Formula = "=IFERROR(VLOOKUP(B2,'quote'!$b:$H,4,FALSE),""0"")"
With ActiveSheet.UsedRange
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
.Cells(1).Select
End With
Application.CutCopyMode = False
With ActiveSheet.UsedRange
.Value = .Value
End With*********
*********Dim rg As Range
With ActiveSheet
Set rg = .Range("B2") 'Put formulas in cells until end of data in column A
Set rg = Range(rg, .Cells(.Rows.Count, rg.Column).End(xlUp))
rg.Offset(0, 8).Formula = "=IFERROR(VLOOKUP(B2,'quote'!$b:$H,4,FALSE),""0"")"
With ActiveSheet.UsedRange
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
.Cells(1).Select
End With
Application.CutCopyMode = False
With ActiveSheet.UsedRange
.Value = .Value
End With*********