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:
Below what I am trying to insert into a range:
Can someone please help. Thank you
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:
VBA Code:
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:
VBA Code:
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