Vlookup Conundrum

L

Ladred

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


PHP code:
--------------------
=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.


PHP code:
--------------------
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
--------------------
 
D

Don Guillett

Can't you just make the lookup range absolute $A$1:$D$781 or give it a name
and use that? Then, just copy down.

--
Don Guillett
SalesAid Software
(e-mail address removed)
Ladred said:
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


PHP code:
Click to expand...
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.


PHP code:
--------------------
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],
Click to expand...
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],
Click to expand...
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],
Click to expand...
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))))"
 
T

Tom Ogilvy

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 said:
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


PHP code:
Click to expand...
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.


PHP code:
--------------------
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],
Click to expand...
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],
Click to expand...
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],
Click to expand...
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))))"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro autofills formula when a cell value is changed 4
Data samples 3
macro to copy rows that contain a cell with a certain number 2
"=TEXT(RC[-1],"MMM")" 4
VBA Compare part of cell to another cell and then calculate 0
Vlookup in vba - how to use absolute rows not relative 1
nested if vba problem 3
VLOOKUP 4

Top