Option
Explicit
Sub
Test()
Dim
wks1
As
Excel.Worksheet
Dim
wks2
As
Excel.Worksheet
Dim
rng1
As
Excel.Range
Dim
rng2
As
Excel.Range
Dim
strFormula
As
String
Set
wks1 = ThisWorkbook.Worksheets(
"Tabelle1"
)
Set
wks2 = ThisWorkbook.Worksheets(
"Tabelle2"
)
Set
rng1 = wks1.Cells(wks1.Rows.Count,
"A"
).
End
(xlUp)
Set
rng1 = wks1.Range(wks1.Range(
"A1"
), rng1)
Set
rng2 = wks2.Columns(
"A:B"
)
With
rng1.Offset(ColumnOffset:=3)
strFormula =
"VLOOKUP(RC[-3],"
& rng2.Address(ReferenceStyle:=xlR1C1, External:=
True
) &
",2,FALSE)"
strFormula =
"=IF(ISERROR("
& strFormula &
"),"
""
","
& strFormula &
")"
.FormulaR1C1 = strFormula
End
With
With
rng1.Offset(ColumnOffset:=2)
.FormulaR1C1 =
"=IF(RC[1]<>"
""
",RC[-2],"
""
")"
End
With
End
Sub