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



--
Hope that helps.

Vergel Adriano


"Vergel Adriano" wrote:

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?


--
Hope that helps.

Vergel Adriano


"DB74" wrote:

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?

"Vergel Adriano" wrote:

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


--
Hope that helps.

Vergel Adriano


"DB74" wrote:

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??