Need help with VBA

[SLElephant]

I am trying to get a user defined sheet name to work for a xlookup. When I hard code the sheet name it works But when I try to use a name defined it doesn't

Dim CurMonth As Integer
Dim PreMonth As Integer
Dim PreCycleTime As String
Dim PreCPHRate As String

' InsertSheets
'
Sheets.Add before:=Sheets(1)
CurMonth = InputBox("Enter Two Digit Numarical Current Month and Press Enter")

PreMonth = CurMonth - 1

PreCycleTime = ("Cycle Times 2023." & PreMonth)

'***************************************************************************************************************************

Sheets(1).Name = "CPH Rates 2023." & CurMonth

Sheets.Add After:=Sheets(1)
Sheets(2).Name = "Cycle Times 2023." & CurMonth


Sheets("Cycle Times 2023." & CurMonth).Select
'
'

'
'Cycle times Populate
'

THIS WORKS

ActiveCell.Formula = _
"=XLOOKUP(A2&B2&C2, 'Cycle Times 2023.06'!A:A & 'Cycle Times 2023.06'!B:B & 'Cycle Times 2023.06'!C:C, 'Cycle Times 2023.06'!K:K)"


Not THIS

Range("N2").Select
ActiveCell.Formula = _
"=XLOOKUP(A2&B2&C2, 'PreCycleTime'!A:A&'PreCycleTime'!B:B&'PreCycleTime'!C:C,'PreCycleTime'!L:L)"
'

 

[MARK858]

Untested but maybe...

Range("N2").Formula = _
"=XLOOKUP(A2&B2&C2,'" & PreCycleTime & "'!A:A&'PreCycleTime'!B:B&'" & PreCycleTime & "'!C:C,'" & PreCycleTime & "'!L:L)"

 

[rlv01]

Cycle Times 2023.06 is the name of a worksheet. PreCycleTime is a variable containing the name of a worksheet. The two are treated differently when using vba to add a formula.

Minor correction to @MARK858 's formula

Range("N2").Formula = _
"=XLOOKUP(A2&B2&C2,'" & PreCycleTime & "'!A:A & '" & PreCycleTime & "'!B:B & '" & PreCycleTime & "'!C:C, '" & PreCycleTime & "'!K:K)"

Since from a VBA perspective, a formula is just a string, I often find it convenient to use a string variable to build the formula before writing it to a cell. That way I can use the debugger to inspect it to be sure it is what I want.

Dim FormulaStr As String
FormulaStr = "=XLOOKUP(A2&B2&C2,'" & PreCycleTime & "'!A:A & '" & PreCycleTime & "'!B:B & '" & PreCycleTime & "'!C:C, '" & PreCycleTime & "'!K:K)"
Debug.Print FormulaStr
Range("N2").Formula = FormulaStr

 

[MARK858]

@rlv01 thanks for picking up the one I missed

 

[SLElephant]

Untested but maybe...

Range("N2").Formula = _
"=XLOOKUP(A2&B2&C2,'" & PreCycleTime & "'!A:A&'PreCycleTime'!B:B&'" & PreCycleTime & "'!C:C,'" & PreCycleTime & "'!L:L)"

It worked Thank you so much

 

[SLElephant]

@rlv01 thanks for picking up the one I missed

It worked Thank you so much

 

[SLElephant]

Cycle Times 2023.06 is the name of a worksheet. PreCycleTime is a variable containing the name of a worksheet. The two are treated differently when using vba to add a formula.

Minor correction to @MARK858 's formula

Range("N2").Formula = _
"=XLOOKUP(A2&B2&C2,'" & PreCycleTime & "'!A:A & '" & PreCycleTime & "'!B:B & '" & PreCycleTime & "'!C:C, '" & PreCycleTime & "'!K:K)"

Since from a VBA perspective, a formula is just a string, I often find it convenient to use a string variable to build the formula before writing it to a cell. That way I can use the debugger to inspect it to be sure it is what I want.

Dim FormulaStr As String
FormulaStr = "=XLOOKUP(A2&B2&C2,'" & PreCycleTime & "'!A:A & '" & PreCycleTime & "'!B:B & '" & PreCycleTime & "'!C:C, '" & PreCycleTime & "'!K:K)"
Debug.Print FormulaStr
Range("N2").Formula = FormulaStr

It worked Thank you so much

 

[MARK858]

It worked Thank you so much

@SLElephant the post by @rlv01 is the correct solution (post 3), so really you should mark that one as the solution rather than my post (post 2)

 

[SLElephant]

Did that, Thanks for pointing out