Help with VBA syntax

[JARichard74]

I have this formula. I keep getting application-defined or object-defined error 1004

.Range(.Cells(k * z + j + 3 - z, 2), .Cells(k * z + j + 3 - z, 2)).Formula = "=XLOOKUP(Price!$A" & (k * z + j + 3 - z) & ",Bids!R1:BA1,XLOOKUP(Price!$A$" & (k * z + 3 - z) & ",Bids!I3:I8,OFFSET(Bids!R3:BA8,0,6)),,2)"

That I want to change as follows:
Bids!R1:BA1 refers to Bids!Cells(1, 18), Cells(1, lColumn)
Bids!I3:I8 refers to Bids!Cells(3, 8), Cells(lRow, 8)
Bids!R3:BA8 refers to Bids!Cells(3, 18), Cells(lRow, lColumn)

Where
lRow = Range("I1").End(xlDown).Row
lColumn = Range("A2").End(xlToRight).Column

Thanks

 

[GWteB]

The ranges within your line of code (eg .Range and .Cells) are not qualified. You are supposed to use the With ... End With control structure.

With ThisWorkbook.Worksheets("Sheet1")
    .Range(.Cells(k * z + j + 3 - z, 2), .Cells(k * z + j + 3 - z, 2)).Formula = "=XLOOKUP(Price!$A" & (k * z + j + 3 - z) & ",Bids!R1:BA1,XLOOKUP(Price!$A$" & (k * z + 3 - z) & ",Bids!I3:I8,OFFSET(Bids!R3:BA8,0,6)),,2)"
End With

 

[JARichard74]

The ranges within your line of code (eg .Range and .Cells) are not qualified. You are supposed to use the With ... End With control structure.

With ThisWorkbook.Worksheets("Sheet1")
    .Range(.Cells(k * z + j + 3 - z, 2), .Cells(k * z + j + 3 - z, 2)).Formula = "=XLOOKUP(Price!$A" & (k * z + j + 3 - z) & ",Bids!R1:BA1,XLOOKUP(Price!$A$" & (k * z + 3 - z) & ",Bids!I3:I8,OFFSET(Bids!R3:BA8,0,6)),,2)"
End With

It is. I get a syntax error in the Formula

With Worksheets("Price")
For k = 1 To y 'y = Number of Price criteria
    For j = 1 To z - 1 'z-1 = Number of Proponents
    .Range(.Cells(k * z + j + 3 - z, 2), .Cells(k * z + j + 3 - z, 2)).Formula = "=XLOOKUP(Price!$A" & (k * z + j + 3 - z) & ",Bids!R1:BA1,XLOOKUP(Price!$A$" & (k * z + 3 - z) & ",Bids!I3:I11,OFFSET(Bids!R3:BA11,0,6)),,2)"
    Next j
Next k
End With

 

[JARichard74]

It is. I get a syntax error in the Formula

With Worksheets("Price")
For k = 1 To y 'y = Number of Price criteria
    For j = 1 To z - 1 'z-1 = Number of Proponents
    .Range(.Cells(k * z + j + 3 - z, 2), .Cells(k * z + j + 3 - z, 2)).Formula = "=XLOOKUP(Price!$A" & (k * z + j + 3 - z) & ",Bids!R1:BA1,XLOOKUP(Price!$A$" & (k * z + 3 - z) & ",Bids!I3:I11,OFFSET(Bids!R3:BA11,0,6)),,2)"
    Next j
Next k
End With

Syntax error when I try to go to the last row and last column instead of the specified ranges

 

[StephenCrump]

I think this is what you're trying to do?

'Values for testing purposes only
k = 2
j = 4
Z = 3
RowNo = k * Z + j + 3 - Z   '=10 here
lRow = 12
lColumn = 30

With Worksheets("Price")
    '....
    .Range("B" & RowNo).Formula2 = "=XLOOKUP(Price!$A" & RowNo & ",Bids!R1:" & Cells(1, lColumn).Address & ",XLOOKUP(Price!$A$" & RowNo & ",Bids!I3:I" & lRow & ",Bids!R3:" & Cells(lRow, lColumn).Address & "),,2)"
    '....
End With

which for the test values shown, will generate the formula:

B10: =XLOOKUP(Price!$A10,Bids!R1:$AD$1,XLOOKUP(Price!$A$10,Bids!I3:I12,Bids!R3:$AD$12),,2)

It's not clear why you're using OFFSET, and the formula won't work as written, so I have stripped it out for the moment.

 

[JARichard74]

I think this is what you're trying to do?

'Values for testing purposes only
k = 2
j = 4
Z = 3
RowNo = k * Z + j + 3 - Z   '=10 here
lRow = 12
lColumn = 30

With Worksheets("Price")
    '....
    .Range("B" & RowNo).Formula2 = "=XLOOKUP(Price!$A" & RowNo & ",Bids!R1:" & Cells(1, lColumn).Address & ",XLOOKUP(Price!$A$" & RowNo & ",Bids!I3:I" & lRow & ",Bids!R3:" & Cells(lRow, lColumn).Address & "),,2)"
    '....
End With

which for the test values shown, will generate the formula:

B10: =XLOOKUP(Price!$A10,Bids!R1:$AD$1,XLOOKUP(Price!$A$10,Bids!I3:I12,Bids!R3:$AD$12),,2)

It's not clear why you're using OFFSET, and the formula won't work as written, so I have stripped it out for the moment.

As an example, I want to match the Description on another Sheet/Table (See Clipboard02) with the description on the sheet called Bids(See Clipboard01), then match the vendor from the same other sheet to this sheet called Bids. At the intersection, offset to get the matching price in Column X. The formula works perfectly at my end but only for the sample sheet Bids I had which contains 9 descriptions and 4 vendors. I need the formula to adjust if I have more or fewer vendors and more or fewer Descriptions

 

[StephenCrump]

You can offset 6 columns like this:

Const OFFSET = 6

'Values for testing purposes only
k = 2
j = 4
Z = 3
RowNo = k * Z + j + 3 - Z   '=10 here
lRow = 12
lColumn = 30
With Worksheets("Price")
    '....
    .Range("B" & RowNo).Formula2 = "=OFFSET(XLOOKUP(Price!$A" & RowNo & ",Bids!R1:" & Cells(1, lColumn).Address & ",XLOOKUP(Price!$A$" & RowNo & ",Bids!I3:I" & lRow & ",Bids!R3:" & Cells(lRow, lColumn).Address & ",,2)),," & OFFSET & ")"
    'OR
    .Range("B" & RowNo).Formula2 = "=INDEX(XLOOKUP(Price!$A$" & RowNo & ",Bids!I3:I" & lRow & ",Bids!R3:" & Cells(lRow, lColumn).Address & ",,2),MATCH(Price!$A" & RowNo & ",Bids!R1:" & Cells(1, lColumn).Address & ",)+" & OFFSET & ")"
    '....
End With

Based on your original post, this is using the same lookup_value for both horizontal and vertical lookups. You'll need to adjust these references as required.

 

[JARichard74]

You can offset 6 columns like this:

Const OFFSET = 6

'Values for testing purposes only
k = 2
j = 4
Z = 3
RowNo = k * Z + j + 3 - Z   '=10 here
lRow = 12
lColumn = 30
With Worksheets("Price")
    '....
    .Range("B" & RowNo).Formula2 = "=OFFSET(XLOOKUP(Price!$A" & RowNo & ",Bids!R1:" & Cells(1, lColumn).Address & ",XLOOKUP(Price!$A$" & RowNo & ",Bids!I3:I" & lRow & ",Bids!R3:" & Cells(lRow, lColumn).Address & ",,2)),," & OFFSET & ")"
    'OR
    .Range("B" & RowNo).Formula2 = "=INDEX(XLOOKUP(Price!$A$" & RowNo & ",Bids!I3:I" & lRow & ",Bids!R3:" & Cells(lRow, lColumn).Address & ",,2),MATCH(Price!$A" & RowNo & ",Bids!R1:" & Cells(1, lColumn).Address & ",)+" & OFFSET & ")"
    '....
End With

Based on your original post, this is using the same lookup_value for both horizontal and vertical lookups. You'll need to adjust these references as required.

Thanks, I will test it tomorrow.

 

[JARichard74]

Thanks, I will test it tomorrow.

Seems to work, thanks.

 

[StephenCrump]

Great, thanks for posting back.