Do...Loop syntax help?

[Guest]

Here is the current code I am using:

x = 2
Do Until Cells(x, 1).Value <> ""
Cells(x, 5).Value = "IF(LOOKUP F2,'SORTING
CRITERA'!B:B)=F2,LOOKUP(F2,'SORTING CRITERA'!B:B,'SORTING CRITERA'!A:A),0)"
x = x + 1
Loop

I am looking for the function to change relative to the current row...right
now in row 2 it is correct, but I would like the Lookup to change to F3 in
row 3, F4 in row 4 ect...

Any suggestions??

 

[Guest]

Instead of looping, maybe use the FillDown method. something like this:

Dim lLastRow As Integer
lLastRow = Range("B65536").End(xlUp).Row
Range("B5").Value = "=IF(LOOKUP(F2,'SORTING
CRITERA'!B:B)=F2,LOOKUP(F2,'SORTING CRITERA'!B:B,'SORTING CRITERA'!A:A),0)"
Range("B5:B" & lLastRow).FillDown

 

[Guest]

Thank you for your reply Vergel...When I run this, it fills down to the end
of the sheet which I am trying to avoid, I just want it to fill down to the
end of my data (which varies)...any suggestion?

 

[Guest]

This line

lLastRow = Range("B65536").End(xlUp).Row

should have determined the last row of data. Can you post the code that you
have now?

 

[Guest]

I looked at your code again and I incorrectly used column B in my suggested
code.. try it like this instead:

Dim lLastRow As Long

lLastRow = Range("A65536").End(xlUp).Row
Range("E5").Value = "=IF(LOOKUP(F2,'SORTING
CRITERA'!B:B)=F2,LOOKUP(F2,'SORTING CRITERA'!B:B,'SORTING CRITERA'!A:A),0)"
Range("E5:E" & lLastRow).FillDown

 

[Dana DeLouis]

I am looking for the function to change relative to the current row....

I see you have a solution. Here is just a technique...

Enter a working function in the spreadsheet.

Cells(x, 5).Value = "IF(LOOKUP F2,'SORTING

You will see that you are probably missing a "=" and "(".
Perhaps..."=IF(LOOKUP(F2,'SORTING...

Swithch from A1 display, to R1C1 Display, and copy that formula.

str = "=IF(LOOKUP(RC[1],...etc"

Then...
Cells(R, 5).FormulaR1C1 = str

Again, just a technique.