LOOKUP formula help - choppy formula ..how to make it more efficient?

**donyc** · 03-23-2011, 10:59 AM

Hi all, I am having some trouble with LOOKUP formula. What am I doing wrong here?

The formula I use, looks up specific value based on 2 identifiers.

The problem:
1) When my data gets transferred in K3.. I need to drag it down all the way down..which i have implemented in the code below.. lets says I have 100 rows in my 1st tab and it looks up against other tab where it has 250 rows.. the problem I have is although it transfers the correct data, it keeps dragging down past 100 rows and brings remaining 150 values(as 0 though because it did not find the matches I assume)

2) the number of rows change daily.. currently I have it set 3:10001. Is there any way to incorporate Last Row into this so it scans everything up to last row? skipping blanks ofcourse..

Please Login or Register  to view this content.

Any ways around these 2 issues?

Thanks so much

**pike** · 03-24-2011, 03:58 AM

Hi donyc
Try....

Please Login or Register  to view this content.

**donyc** · 03-24-2011, 10:42 AM

Hi Pike, I tried it but it does not drag down the formul all the way down for me..below is the full code I use

Please Login or Register  to view this content.

**JBeaucaire** · 03-24-2011, 11:34 AM

It looks like you're getting a "lastrow" answer from one sheet. If you want to apply the formula on the second sheet to a specific "lastrow" on that sheet, you would need to add another variable.

Add a "LastRow2" variable an solve it on the second sheet, then insert that variable into the second half of your paste range.

**markflash** · 03-24-2011, 12:28 PM

ive got a formula which looks up on a pivot table but i wish to replace one fo the fields to represent a square on the file, which in turn i could change and then this would then change which dats field it looks up on the pivot chart ?

is this possible ?

basically,

=GETPIVOTDATA("Value",Pivot!$A$4,"Day","Mon","Wk Yr","201112","A/C Desc","Dry")

this is the formula and i wish to change the 201112 to represent the figure which is imputted into square f19 ?

**donyc** · 03-24-2011, 12:37 PM

didn't work

.. shouldnt the below in red be applied based on last row in the sheet I am inputting the formula in? why is it referring last row from sheet2? I never defined lastrow there..

Dim LastRow2 As Long
LastRow2 = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

Please Login or Register  to view this content.

**JBeaucaire** · 03-24-2011, 07:07 PM

You dimmed the Lastrow2 variable, that's good. But then you fill in the variable right away at the top of the macro. Are you sure you're on the correct sheet when you store that number? Add a reference to that command so we know it's being applied to the correct sheet at the correct time.

Also, I thought you needed the original LastRow reference for something else. Don't you? I thought we were adding a new variable here, not just replacing the old, no point in that, really.