Vlookup + Iferror on listObjects

[Rita F]

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

 

[jasonb75]

Do you want to refer to is as a listobject, or just by name? If the tables already exist then using name would be the easier method (as long as nobody changes the name afterwards).

Table names can be used in vba in the same way as any normal range, for example to use a column named 'Date' in 'Table1' you would use

With  .Range("Table1[Date]")

In the formula you would write it the same way as if you enter the formula into the sheet manually, for that you could acquire the code from the macro recorder.

 

[Rita F]

Do you want to refer to is as a listobject, or just by name? If the tables already exist then using name would be the easier method (as long as nobody changes the name afterwards).

Table names can be used in vba in the same way as any normal range, for example to use a column named 'Date' in 'Table1' you would use

With  .Range("Table1[Date]")

In the formula you would write it the same way as if you enter the formula into the sheet manually, for that you could acquire the code from the macro recorder.

I know it, but this case is much complicated than just a name...

 

[jasonb75]

Based on your brief question it should be adequate, if it is not then we are going to need more information on what you are trying to do before we will be able to identify a suitable alternative.

 

[Rita F]

Based on your brief question it should be adequate, if it is not then we are going to need more information on what you are trying to do before we will be able to identify a suitable alternative.

It just what i said. i have table X and need to lookup for value from table Y , like on picture bellow

 

[jasonb75]

I can't see the name of the table in the capture so I've used Table1 in the code. Also, I don't have the (Hindi?) characters used in one of the table names on my laptop in order to retype your exact formula so I've had to substitute that part with [????]

As far as I can see there is no reason for it not to work with table names. From the information that you have provided, this should work just fine once you correct the names as required.

With Activesheet
    With .Range("Table1[QuantityBP]")
        .Formula= "=IFERROR(VLOOKUP([@LookUpValue],BynetQ[[LookUpValue]:[????]],4,0),0)"
        .Value = .Value
    End With
End With

 

[Rita F]

It worked! Thank you!!