Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel, vba and construction of a formula that updates properly
i am constructing my inventory controls using excel and vba
i have 14 tables that contain inventory data for each warehouse (2 warehouses altogether) the first table is YTD inventory information (ie the starting inventory level, total product sold, total product sent as samples, and ending inventory level (ie current inventory). the second table is monthly inventory information (ie the start inventory level for the month, total sold for the month, total samples for the month, and ending level for the month tables 3-14 are the inventory levels for each month. table 2 uses a validation box to choose the month that the inventory level is to be displayed. once the month is chosen, formulas are used to find the chosen month among tables 3-14, and select the corresponding value and put it into table 2 an example of table 2 K L M N O P Q R 2 March 3 Code Lot# St_Case Dt_Case Sd_Case Td_Case Se_Case Eg_Case 4 FC112 54733 10 0 0 0 0 10 5 FC116 57762 5 0 0 0 0 5 6 Totals 15 0 0 0 0 15 8 March 9 Code Lot# St_Case Dt_Case Sd_Case Td_Case Se_Case Eg_Case 10 FC112 54733 1 0 0 0 0 1 11 FC116 57762 5 0 0 0 0 5 12 Totals 6 0 0 0 0 6 the bottom table is the table in question and it gets its data from an identically formed table, where the corresponding value of K8 starts in T8 my code for the top warehouse works perfectly. my code for the bottom warehouse is what i am having trouble with. when i add a new product line, lines (rows) are inserted and the formulas are added for the new product line. i am using the following vb code to set the formula of the cell. <BEGIN VB CODE loopRange.Offset(0, 11).FormulaR1C1 = "=offset(indirect(address(" & 7 + numpr & ",19+match(r[" & numpr - 2 & "]c11,r[" & numpr - 2 & "]c20:r[" & numpr - 2 & "]c108,0),4))," & 2 + numpr & ",2)" <END VB CODE (numpr is the number of product codes already in the inventory) when a new product line is added, 2 rows are inserted. one for each of the warehouses - and in alphabetical order (ie if FC114 would be the next product line then the row would be inserted between FC112 and FC116 - row 5 would contain FC114 as would row 12) my problem is in the & 7 + numpr & part of the above code - it works fine until a row is inserted and then it referrs to an incorrect cell since 7 + numpr does not update on the lines above. is there a way to make that part of this formula relative so that when a row is inserted it updates correctly? if need be i can upload my project to a website and insert a breakpoint at the line in question. is there a better way to construct my formula so that it does update correctly when a row is inserted? or is my only option to re-do the formulas after the product line has been added (there will be ( 6 x the final number of products - 6) formulas to update) thanks! J |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I am looking for formula to calculate escalation on construction | Excel Discussion (Misc queries) | |||
formula construction | New Users to Excel | |||
formula construction | Excel Discussion (Misc queries) | |||
Construction Loan Interest Formula | Excel Worksheet Functions | |||
formula construction | Excel Programming |