Inserting a Formula to a Range using VBA

[AndyJM87]

Hello,

I am struggling to insert a VLOOKUP formula using VBA which references another sheet in a workbook.

I have a User Form which loads the file path and file name then stores them as a global variable.

strL & strF are global variables defined as a string.

Below is the Form code to insert the file into a textbox:

Private Sub cmdStdBrowse_Click()
On Error Resume Next

Dim f           As Object
Dim strFile     As String
Dim strFolder   As String
Dim varItem     As Variant

    Set f = Application.FileDialog(3)
        f.AllowMultiSelect = False
            If f.Show Then
                For Each varItem In f.SelectedItems
                    strFile = Dir(varItem)
                    strFolder = Left(varItem, Len(varItem) - Len(strFile))
                    txtStdProd.Text = strFolder & strFile
                    
                    strL = strFolder
                    strF = strFile
                Next
        End If
    Set f = Nothing

End Sub

Below what I am trying to insert into a range:

Private Sub cmdInsert_Click()
On Error Resume Next

Set wb = ActiveWorkbook

    'Call wbStart
    
    'MsgBox (strL & strF)
    
    With wb.Sheets("REPORT")
        .Range("F3").Formula = "=IFERROR(VLOOKUP($A3,' & strL & [  & strF & ]Component List for Equipment'!$D$15:$P$150,13,FALSE),"")"
    End With
    
    Unload Me
    
End Sub

Can someone please help. Thank you

 

[offthelip]

I have found that the easiest way to debug writing formula with vBA is to remove the equals sign from the vba e.g.:

.Range("F3").Formula = "IFERROR(VLOOKUP($A3,' & strL & [  & strF & ]Component List for Equipment'!$D$15:$P$150,13,FALSE),"")"

this then just writes a string to the cell. Then select the cell and put the = back in , then excel will show you where the error is.
Teach a man to fish , etc..

 

[Alex Blakenburg]

@AndyJM87
I like @offthelip's method to check the vba generated formula.

You have your 2 variables embedded inside the quotation marks, this means they are read as text and not replaced by their values.
You need to break the string up and put it back together with the translation of the variables.
You also need to double up on the quote marks for the empty string return value
eg

.Range("F3").Formula = "=IFERROR(VLOOKUP($A3,'" & strL & "[" & strF & "]Component List for Equipment'!$D$15:$P$150,13,FALSE),"""")"

I assume you have the 2 variables Dimmed at a Public / Global level and that you have tested it with the now commented out MsgBox (strL & strF)