CODEWITHSUNDEEP
vba
John K
John K

Reputation: 33

Browse file path and should add it to Vlookup for Reference using VBA

Hi I need to be able to browse a file and add it in vlookup formula for reference using vba...here is my code...please help I am able to pick a file, but the problem is double time opening of FileDialog folder for selection.

Private Sub CommandButton2_Click()
Range("Q2").Select
FilePath = Application.GetOpenFilename("Excel Files (*.xlsx), *.xls")
If FilePath <> False Then
Range("D6").Value = FilePath
ActiveCell.FormulaR1C1 = _
"=VLookup(RC[-13]:R[68]C[-13],'FilePath'!R2C2:R994C6,5,False)"

Range("Q2").Select
Selection.Copy
Range("P2").Select
Selection.End(xlDown).Select
Selection.Offset(0, 1).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("Q:Q").Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Range("Q1").Select
Selection.Value = "Payment Method"
Range("Q2").Select

Dim AutoRange As Range
Dim i As Long

Set AutoRange = Range("Q:Q")
   For i = 1 To 8000
   If AutoRange.Cells(i).Value = "0" Then
   AutoRange.Cells(i).Value = "Online"
   Else
   End If
   Next i
ActiveWorkbook.Save
End Sub

Upvotes: 0

Views: 1358

Answers (1)

TheKornfleis
TheKornfleis

Reputation: 26

I think you are forgetting to name the worksheet from where you want yo search your information in your vlookup formula. Let's assume the data is stored in "sheet1" for every workbook that is eligible:

FilePath_final = Left(FilePath, InStrRev(FilePath, "\")) + "[" + Right(FilePath, Len(FilePath) - InStrRev(FilePath, "\")) + "]"
' That is to put the "[" and "]" before and after the workbook name

ActiveCell.FormulaR1C1 = _
"=VLookup(RC[-13]:R[68]C[-13],'" + FilePath_final + "sheet1'!R2C2:R994C6,5,False)"

Try those 2 lines instead of your vlookup line

Upvotes: 1

Related Questions