| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup Conundrum
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/ |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Vlookup Conundrum
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/ |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conundrum | Excel Discussion (Misc queries) | |||
| COUNTIF conundrum | Excel Worksheet Functions | |||
| Help!! NEW Conundrum for stats!? | Excel Discussion (Misc queries) | |||
| VBA conundrum | New Users to Excel | |||
| ISERROR Conundrum | Excel Worksheet Functions | |||
Linear Mode
