Use absolute cell references:
=IF(IF(ISERR(LEFT(A45, FIND(" ", A45) -1)), A45, LEFT(A45, FIND(" ",
A45) -1))="LOC", "", IF(A45=0, "", (VLOOKUP(A45,'unit
prices'!$A$1:$D$781,2,FALSE))))
--
Regards,
Tom Ogilvy
"Ladred" wrote in message
...
vlookup conundrum.
I have an error checking vlookup for an invoice sheet. When I enter the
item number it spits out the description from a unit prices sheet. When
I enter in the amount of items used it spits out the units, and
multplier and totals the line. Now my problem is that occassionally
I'll get more items than I have rows so I need to quickly add a new row
with identical properties. So I've tried to make a macro to do this
quickly.
Here is the formula for the second column on row 45
Formula:
--------------------
=IF(IF(ISERR(LEFT(A45, FIND(" ", A45) -1)), A45, LEFT(A45, FIND(" ",
A45) -1))="LOC", "", IF(A45=0, "", (VLOOKUP(A45,'unit
prices'!A1:D781,2,FALSE))))
--------------------
Now what I've tried to do is offset one row, insert a new row and apply
that function to the new row second column. The problem that I'm
getting is that it puts the correct cell number to compare the product
number with, but it also adds one to the range of the vlookup. How can
I prevent it from adding one to the range of the vlookup, i.e. I need
the vlookup to be static. Here is a copy of my so far feeble attempt.
Formula:
--------------------
ActiveCell.Offset(1).EntireRow.Insert
ActiveCell.End(xlToLeft).Offset(1, 0).Activate
ActiveCell.Offset(0, 1).Activate
ActiveCell.FormulaR1C1 = _
"=IF(IF(ISERR(LEFT(RC[-1], FIND("" "", RC[-1]) -1)), RC[-1],
LEFT(RC[-1], FIND("" "", RC[-1]) -1))=""LOC"", """", IF(RC[-1]=0, """",
(VLOOKUP(RC[-1],'unit prices'!R[-46]C[-1]:R[734]C[2],2,FALSE))))"
ActiveCell.Offset(0, 2).Activate
ActiveCell.FormulaR1C1 = _
"=IF(IF(ISERR(LEFT(RC[-3], FIND("" "", RC[-3]) -1)), RC[-3],
LEFT(RC[-3], FIND("" "", RC[-3]) -1))=""LOC"", """", IF(RC[-3]=0, """",
(VLOOKUP(RC[-3],'unit prices'!R[-46]C[-1]:R[734]C[2],3,FALSE))))"
ActiveCell.Offset(0, 1).Activate
ActiveCell.FormulaR1C1 = _
"=IF(IF(ISERR(LEFT(RC[-4], FIND("" "", RC[-4]) -1)), RC[-4],
LEFT(RC[-4], FIND("" "", RC[-4]) -1))=""LOC"", """", IF(RC[-4]=0, """",
(VLOOKUP(RC[-4],'unit prices'!R[-46]C[-1]:R[734]C[2],3,FALSE))))"
ActiveCell.End(xlToLeft).Activate
--------------------
------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/