Excel VBA Variable File Name in Lookup Formula

amcintosh

New Member
Joined
May 17, 2010
Messages
4
Hi

I'm trying to use VBA to insert a formula into in excel spreadsheet that contains file path

Snippit of the codes

Code:
Dim sdpath As String, sdfile As String
sdpath = "C:\Users\amcin\OneDrive\Work Ops\VBA Projects\Sales Report\"
sdfile = "Sales Report Data.xlsx"

Range("B" & lr).FormulaR1C1 = "=Vlookup(RC[2],'[" & sdpath & sdfile & "]Data'!C1:C2,2,FALSE)

Returns this in the Excel Spreadsheet:
=VLOOKUP(D64,'[C:\Users\amcin\OneDrive\Work Ops\VBA Projects\Sales Report\[Sales Report Data.xlsx]Data]Sales Report Data.xlsx]Data'!$A:$B,2,FALSE)

anybody got an idea so it'll look like this:
=VLOOKUP(D64,'[C:\Users\amcin\OneDrive\Work Ops\VBA Projects\Sales Report\[Sales Report Data.xlsx]Data'!$A:$B,2,FALSE)

Cheers

PS This is the full code - however this is still a work in progress
Code:
Dim w As Workbook, w2 As WorkbookDim ed As Worksheet
Dim sdpath As String, sdfile As String


Sub opendatafile()


'Choose Sales Report Path here.
'development - Andrew's home PC
sdpath = "C:\Users\amcin\OneDrive\Work Ops\VBA Projects\Sales Report\"
On Error GoTo open1
sdfile = "Sales Report Data.xlsx"
Set w = Application.ActiveWorkbook
Set w2 = Workbooks(sdfile)
Set ed = w2.Worksheets("Data")




    w2.Activate 'activates the sales report data
    w.Activate 'activates this workbook
Exit Sub


open1:
Workbooks.Open (sdpath & sdfile)
w.Activate


End Sub




Sub import()
Application.ScreenUpdating = False
Application.CutCopyMode = False






Call opendatafile




Range(Columns(2), Columns(3)).Insert shift:=xlRight
Cells(1, 2).Value = "Department"
Cells(1, 3).Value = "Customer"
Columns(12).Delete
Cells(1, 12).Value = "Buy Price"


Dim lr As Long


'data clean up


lr = 2000


For lr = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
Dim DDD As String
    'Insert Department
        Range("B" & lr).FormulaR1C1 = "=Vlookup(RC[2],'[" & sdpath & sdfile & "]Data'!C1:C2,2,FALSE)"


        
'DDD = "'C:\Users\amcin\OneDrive\Work Ops\VBA Projects\Sales Report\Sales Report Data.xlsx'"
        'Range("B" & lr).Formula = "=vlookup(D" & lr & ",'[" & DDD & "]Data'!$A:$B,2,false)"


    'Insert Customers
        Range("C" & lr).FormulaR1C1 = "=VLOOKUP(RC[3],'[Sales Report Data.xlsx]Data'!R1C19:R5C20,2,FALSE)"
                
    'Delete Zero Quantity Line Lines
    If Cells(lr, 10).Value = 0 Then Rows(lr).EntireRow.Interior.ColorIndex = 7
    
    'Mark Credits
    If Cells(lr, 10).Value < 0 Then
    With Cells(lr, 2)
        .Value = "CREDIT"
        .Interior.ColorIndex = 45
        .Font.Bold = True
        End With
    End If
    
    'Delete Crates
    If Left(Cells(lr, 4).Value, 3) = "ZZ5" Then Rows(lr).EntireRow.Interior.ColorIndex = 35
    
    'Delete N/A Customer Lines
    If WorksheetFunction.IsNA(Cells(lr, 3)) = True Then Rows(lr).EntireRow.Interior.ColorIndex = 39
      
    'Delete Blank and GL Cells
    If IsNumeric(Cells(lr, 4)) = True Or IsEmpty(Cells(lr, 4)) = True Then Rows(lr).EntireRow.Interior.ColorIndex = 19






Next lr








Application.ScreenUpdating = True


End Sub
 
Last edited:

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,309
Try...

Code:
Range("B" & lr).FormulaR1C1 = "=Vlookup(RC[2],'" & sdpath & "[" & sdfile & "]Data'!$A:$B,2,FALSE)"

Hope this helps!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,500
Messages
5,529,222
Members
409,857
Latest member
KailuaTown
Top