Transferring updated data

**artham56** · 02-16-2009, 07:46 AM

I am trying to get a macro to update information from attached sheet GTS807 to the Stock sheet. The code in Module 26 works only for the first line and will be very long if I repeat it for the 20 lines of the input sheet! Basically we need to copy the new stock balance from GTS807 column AA13 to AA32 to the individual corresponding product name on the stock sheet (Column E)!
Is there an easier way of doing this either with a more straight forward code or getting my code to run through all the rows?
Please help!

**rylo** · 02-16-2009, 10:21 PM

Hi

Try this

Please Login or Register  to view this content.

HTH

rylo

**artham56** · 02-17-2009, 04:04 AM

Rylo,
Thank you that was perfect - it is so easy when you know what you are doing!!

Just another question - can this same code be used if the stock sheet was a seperate workbook as we have two different types of quote sheet that draw from the same stock. I would obviously need to change the "address" for the stock sheet!
As I trying to teach myself VBA, I sort of understand the code you wrote, but can you tell me what the ce means please.
Thanks again.

**rylo** · 02-17-2009, 07:21 PM

Hi

1) Yep, you can use the same principle. I'd set a variable that would take the name of the workbook/sheet. Something like

Please Login or Register  to view this content.

now when you do your search it would be like

Please Login or Register  to view this content.

2) ce is a variable that takes each instance in the range as it passes through. It could be anything (fred, tom, harry, xxxxx) as it is just a variable holdall.

So what you do is nominate the range that you want to action. In this case it is done with

Please Login or Register  to view this content.

So as you step through each cell in that range, ce takes the instance of the cell. So you can use ce.value to get the cell value, or ce.row to get the cell row and so on. You can have the properties for that cell available using the variable.

Put a breakpoint onto the set findit line, then start the code. If you open an immediate window, then put in ?ce.address, you should see something like $V$13. Experiment with the properties of the variable in the immediate window.

rylo