VBA to write Dynamic Vlookup formula

[Biz]

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.

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

 

[gallen]

on what line do you get the error?

 

[Biz]

The line below
"=VLOOKUP(" & sLookup & ws.Range("A$1").Address & ",Mapping_Table!$A$2:$E$1000,5,0)"""

 

[gallen]

If you store the formula in a string first (sFormula below) you end up with this:

=VLOOKUP(""Test1_Data""&"|"$A$1,Mapping_Table!$A$2:$E$1000,5,0)"

Dim sFormula as string

    sFormula = "=VLOOKUP(" & sLookup & ws.Range("A$1").Address & ",Mapping_Table!$A$2:$E$1000,5,0)"""
    ws.Range("A2:AD2").Formula = sFormula

So as you can see you have double quotes appearing at the end and the first part has too many too.

I'm not sure about the syntax including the "|". If you build the vlookup in a cell first it doesn't come up with that. It comes up with:

=VLOOKUP(Test1_Data!A1,Mapping_Table!$A$2:$E$1000,5,0)

Does that help?

 

[gallen]

After fiddling about, this doesn't give me any errors. Does it work for you?

'*********************************************************************
'**** 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 'not needed
Dim SingleQuote As String 'Not needed
Dim Ampersand As String 'Not needed
Dim sFormula As String




    '*** Declare Variables section is not needed***


    '~~> 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 = ws.Name & "!A1" 'Notice the exclamation, not a pipe
            
            ws.Activate
            
            'Write Vlookup Dynamic Formula
            sFormula = "=VLOOKUP(" & sLookup & ",Mapping_Table!$A$2:$E$1000,5,0)"
            ws.Range("A2:AD2").Formula = sFormula


        End If
    Next ws
    
End Sub

Edit:

Please note that the "|" should have been a "!"

I should have spotted that right away.

 

[Biz]

Hi Mate,

I tried your suggestions and none of them work. My lookup value =B199&"|"&C199 which is lookup defined in my mapping table Inv_Data|doc_type .

My vlookup tries create lookup similar Inv_Data|doc [Worksheet name |range ("A1")]

Hope it makes sense.

Biz

 

[gallen]

Hi Mate,

I tried your suggestions and none of them work. My lookup value =B199&"|"&C199 which is lookup defined in my mapping table Inv_Data|doc_type .

My vlookup tries create lookup similar Inv_Data|doc [Worksheet name |range ("A1")]

Hope it makes sense.

Biz

OK I've gone about this the wrong way. When you build a working formula in a cell using the formula button, are you saying it generates this which you put in your OP?

'"=VLOOKUP(""Inv_Data""&""|""&R[-1]C,Mapping_Table!R2C1:R1000C5,5,0)"

 

[Biz]

Hi Mate,

Managed to make it work using code below.

'*********************************************************************
'**** 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 DblQuotes2 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
ws.Activate

'~~> Compile Lookup Value excluding working cell refer
sLookup = Chr(34) & ws.Name & Chr(34) & Ampersand & Chr(34) & Chr(124) & Chr(34) & "&A1"

'Write Vlookup Dynamic Formula
ws.Range("A2:AD2").Formula = "=VLOOKUP(" & sLookup & ",Mapping_Table!$A$2:$E$1000,5,0)"

End If
Next ws
End Sub

Thank you helping me out trouble shooting my code.

Kind Regards

Biz