VBA run-time Error when using a variable in XLOOKUP

Question

I am trying to fill a range of cells with a XLOOKUP formula using VBA.

I will be using a different file every day, so I would like to use a variable for the date portion of the filename.

I get

Run-time error 1004: Application-defined or object-defined error

in the last step of filling in column V with the XLOOKUP formula.

Dim lastRow As Long, filename As String, reportDate As String          

Call getReportDate(reportDate) 'UserForm where user enters date of report
 
lastRow = ActiveSheet.Range("J" & Sheets("OCI").Rows.Count).End(xlUp).Row     
filename = "report_name_" & reportDate & ".xlsx"     
 
Range("V2:V" & lastRow).Select 
Selection.Value = "=XLOOKUP(@A:A, ""[" & filename & "]Sheetname""!$L:$L, ""[" & filename & "]Sheetname""!$N:$N)"

Hard coding the filename works.

Range("V2:V" & lastRow).Formula= "=XLOOKUP(@A:A, [" & filename & "]Sheetname!$L:$L, [" & filename & "]Sheetname!$N:$N)" — Scott Craner
– Scott Craner, Commented Feb 14, 2024 at 22:57
Thank you! I had to add single quotes for the filename and sheet name. Here is the final solution that worked: Range("V2:V" & lastRow).Formula= "=XLOOKUP(@A:A, '[" & filename & "]Sheetname'!$L:$L, '[" & filename & "]Sheetname'!$N:$N)" — LER
– LER, Commented Feb 15, 2024 at 0:26

MT1 · Accepted Answer · 2024-02-15 12:18:06Z

0

another method without using select (which is preferred) would be looping through Range("V2:V" & lastRow) like this:

Dim lastRow As Long, filename As String, reportDate As String                  

Call getReportDate(reportDate) 'UserForm where user enters date of report
 
lastRow = ActiveSheet.Range("J" & Sheets("OCI").Rows.Count).End(xlUp).Row         
filename = "report_name_" & reportDate & ".xlsx"         

Dim rng as range, c as range 
set rng = Range("V2:V" & lastRow) 
For each c in rng
c.Formula = "=XLOOKUP(@A:A, ""[" & filename & "]Sheetname""!$L:$L, ""[" & filename & "]Sheetname""!$N:$N)"

Next c

edited Feb 15, 2024 at 12:18

MT1

9552 gold badges14 silver badges24 bronze badges

answered Feb 15, 2024 at 0:42

Kurt

1039 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA run-time Error when using a variable in XLOOKUP

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related