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

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

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

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

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

> 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.
--
Dana DeLouis


"DB74" <(E-Mail Removed)> wrote in message
news:B4919EAD-403D-49B2-AB4F-(E-Mail Removed)...
> 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??
>
>