Excel 2003 Macro Problem

[Guest]

Hi,

I am creating a spreadsheet that allows a user to update information from a form on one sheet to another sheet, called 'Store Records'

e.g.:

Add Sale Form:

Product Name

Product Price

Product Quantity:

And then below that, I get how many of the item is currently in stock (from a cell in the store records sheet) and then take it away from what is entered in the 'Product Quantity:' field above. And then another one that gets how many have been sold (from a cell in the store records sheet) and then adds again what was entered into the 'Product Quantity:' above.

In the store records sheet, I have:

Product Name Product Price Quantity In Stock Quantity Sold

What I want to do is get the values from the add sale form and update the records held in the store records sheet above, using a macro. However, when I do that, it will only update the one I have selected when recording the macro, so if I select another product, it will not go to the correct product line.

Anyone got any ideas on how to resolve this?

thanks!

[Kami-]

Stop 'recording' things with FIXED values and 'code' them with VARIABLES.

[Guest]

Hi,

Would you be able to show me an example?

Thanks!

[NateB]

See Attached Excel Worksheet

Coding isnt "Perfect" as it uses exact reference to a sheet number, but does what is being asked.

Keep in mind there are two named ranges in the sheet; Products and Stock both on the "stock" sheet and the dropdown on the order form is control by the range products. So if you add a product it will update the range and dropdown.

Order.zipFetching info...

[Guest]

Hi NateB,

Thanks, just what I was after :)!

[Guest]

Sorry for bumping this, but I really can't get this to work in my own spreadsheet app. Would someone be willing to go over it with me?

Thanks.

Edited May 11, 2009 by Guest