0

I am trying to make a dynamic Macro that takes the path in cells that users enter and paste them into other cells but its not working

Here are my variables

Dim FRLU As String
Dim LU As String

dir = Range("B1").Value
FRLU = Range("B2").Value
LU = Range("B3").Value

Table:

Enter Working Directory into B1: C:\Users\User1\Documents\Test1
Enter first Workbook into B2: workbook1.xlsx
Enter second Workbook into B3: workbook2.xlsx

This is the script I have that enters the XLookup into the new column:

lngLastRow = Cells(Rows.Count, "H").End(xlUp).Row

Range("N4").Formula = "=XLOOKUP(G4,'dir[FRLU]ColumnG'!$G$2:$G$1000,'dir[FRLU]ColumnE'!$E$2:$E$1000)
Range("N4").Copy Range("N5:N" & lngLastRow)

This gets entered into the cell and is copied all the way through the new column and throws me #N/A:

=XLOOKUP(G4,'dir[FRLU]ColumnG'!$G$2:$G$1000,'dir[FRLU]ColumnE'!$E$2:$E$1000)

This is what I want it to say:

=XLOOKUP(G4,'C:\Users\User1\Documents\Test1[workbook1.xlsx]ColumnG'!$G$2:$G$1000,'C:\Users\User1\Documents\Test1[workbook1.xlsx]ColumnE'!$E$2:$E$1000)
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
Andy H
  • 1
  • Rule of thumb: variables don't belong inside quotes, and need concatenated with `&`. – BigBen Sep 30 '22 at 14:52
  • Sorry about the table, It looked correct when I was editing the post but it doesn't appear as one when posted – Andy H Sep 30 '22 at 14:53
  • Don't use `dir` as a variable name, as that shadows the [`Dir`](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/dir-function) function. – BigBen Sep 30 '22 at 14:54
  • @BigBen I've changed it to cellB1 but now it does: ``` =xlookup(G4,'C:\Users\User1\Documents\Test1\[&FRLU]Finance Reportable Living Un'!$G$2:$G$1000,'C:\Users\User1\Documents\Test1\[&FRLU]Finance Reportable Living Un'!$E$2:$E$1000) ``` – Andy H Sep 30 '22 at 15:04
  • It looks like `FRLU` is still inside quotes. – BigBen Sep 30 '22 at 15:09
  • @BigBen I'm not sure what quotes you are referring to: Range("N4").Formula = "=xlookup(G4,'directory\[FRLU]Finance Reportable Living Un'!$G$2:$G$1000,'directory\[FRLU]Finance Reportable Living Un'!$E$2:$E$1000)" This now causes an endless popup of Update Value:[FRLU] – Andy H Sep 30 '22 at 15:22
  • `FRLU` falls within the outermost `"` quotes. You need `"=XLOOKUP(G4,'directory[" & FRLU & "]Finance....` and so on, where `FRLU` is no longer inside the quotes, and is concatenated with `&`. – BigBen Sep 30 '22 at 15:23

0 Answers0