10-08-2003, 01:10 PM
I have an excel spreadsheet of parts and have built a macro to subtract certain quantities from various parts depending on user input. My only unresolved concern is that the user of this spreadsheet may want to add another part to the list by inserting a row in the middle of this list. When this happens, all of the quantities below the point of insertion are shifted down a row. If the macro is run after this insertion, it subtracts quantities from the wrong cells because it is still addressing the cell in which the quantity existed before the insertion. For example, if part A has a quantity of 50 in cell C6 and the user inserts a row above row 6, then part A's quantity of 50 is now in cell C7. When the macro is run, it still subtracts part A's X number of parts from C6 instead of C7. I just need to know if it is possible to tell the macro that another row has been added so that it can adjust accordingly. Any suggestions?
If you have a last row of the list that will stay a last row, you can create a "named range" within that last row and referrence the range name in the code. The name will stick with the cell(s) when rows are inserted above it. Use: Range("name").Address to access the row and column.
10-10-2003, 12:19 PM
So I just name a range in the last row, then reference everything else with respect to that range? The name will move with the cell if it is shifted down a row?
10-10-2003, 12:28 PM
A name stays with its range even if rows are added above it.
In general, try to avoid referring to specific cells like "A7" in your macros, because things do move around, as you say. If possible, name the cells/ranges you want to refer to, or identify them in some other way - using Find, or going x rows up from the last used row, etc. So in this case, name cell C6 "Quantity_A" and you can be sure that the quantity can always be found with Range("Quantity_A").Value
10-10-2003, 12:47 PM
I must be doing something wrong because I'm still having the same problem. I have coded:
Range("C6").Name = "Qty_A"
Range("Qty_A").Value = Range("Qty_A").Value - 5 'if I wanted to subtract 5 from that cell
When I add a row above row 6, the above code still subtracts 5 from cell C6. I'm not too familiar with VB at all so I'm really struggling through this. Thanks for your help
10-10-2003, 01:13 PM
That's because every time you run the macro, you are resetting the name so it refers to C6 again, thereby kind of defeating the whole point :chuckle:
What you really want to do here is set the name once (in code or manually), and then don't touch it again. Don't refer to C6 again in your code, just refer to Qty_A.
So try this:
* name the cell
* run the macro, referring to Qty_A, make sure it works all right
* add rows above
* make sure you don't rename the cell
* run the macro again, referring to Qty_A, make sure it works all right
and tell me how it goes!
10-10-2003, 01:27 PM
OK, I feel like an idiot for not having realized that myself. :o It is subtracting fine now, thanks again for your help.