Dear All,
I'm stuck trying to write, dynamic vlookup formulas.
Error message 1004 Application-Defined or Object Defined Error.
If I record vlookup formula I get '"=VLOOKUP(""Inv_Data""&""|""&R[-1]C,Mapping_Table!R2C1:R1000C5,5,0)"
because I'm trying write formulas in different worksheet tabs. First reference is Worksheet tab name Inv_Data.
Your help and assistance would be greatly appreciated.
Kind Regards
Biz
I'm stuck trying to write, dynamic vlookup formulas.
Error message 1004 Application-Defined or Object Defined Error.
If I record vlookup formula I get '"=VLOOKUP(""Inv_Data""&""|""&R[-1]C,Mapping_Table!R2C1:R1000C5,5,0)"
because I'm trying write formulas in different worksheet tabs. First reference is Worksheet tab name Inv_Data.
Your help and assistance would be greatly appreciated.
Code:
'*********************************************************************
'**** VBA Update Mapping in worksheet tabs which contains "_Data"*****
'*********************************************************************
Sub UpdateMapping()
Dim ws As Worksheet
Dim lrow As Long
Dim sLookup As String
Dim rng As Range
Dim DblQuotes As String
Dim SingleQuote As String
Dim Ampersand As String
'~~> Declare Variables
DblQuotes2 = Chr(34) & Chr(34) ' """" 'represents 2 double quote ("")
SingleQuote = Chr(39) ' "'" 'represents 1 single quote (')
Ampersand = Chr(38) ' "&" ''represents 1 Ampersand quote (&)
'~~> Get Last Row of Mapping Table
lrow = Sheets("Mapping_Table").Cells(Rows.Count, "A").End(xlUp).Row
'~~> Let's loop and populate through worksheet tab which contains "_Data"
For Each ws In ThisWorkbook.Worksheets
If ws.Name Like "*_Data" Then
'~~> Compile Lookup Value excluding working cell refer
sLookup = DblQuotes2 & ws.Name & DblQuotes2 & Ampersand & Chr(34) & "|" & Chr(34)
ws.Activate
'Write Vlookup Dynamic Formula
ws.Range("A2:AD2").Formula = "=VLOOKUP(" & sLookup & ws.Range("A$1").Address & ",Mapping_Table!$A$2:$E$1000,5,0)"""
'"=VLOOKUP(""Inv_Data""&""|""&R[-1]C,Mapping_Table!R2C1:R1000C5,5,0)"
'=VLOOKUP("Inv_Data"&"|"&A1,Mapping_Table!$A$2:$E$1000,5,0)
End If
Next ws
End Sub
Kind Regards
Biz