repeating a macro in every row (for multiple cells)

[hluk]

hi!

I have been looking around the forum and found this thread to the the closest what I am trying to achieve: http://www.excelforum.com/excel-prog...every-row.html

Basically I have two worksheets, one labeled "data" and the other labeled "calc".

For each row of "data" starting from row 2, I need to copy 5 values (columns AA, L, I, M, P) into the "calc" worksheet (cells D11, D12, H11, H12, H13) respectively.

Then the "calc" sheet automatically does the calculations and we get the result in R10 of the "calc" sheet.

Lastly this resulting value R10 needs to be copied back to the "data" sheet in column AB of each row.

I have recorded the macro doing this for the row 2 as per below:

Please Login or Register  to view this content.

I have tried modifying the code to make it repeat, but no luck...

Is there an easy way to adapt what was posted in http://www.excelforum.com/excel-prog...every-row.html to suit what I am trying to do?

Thank you so much in advanced for your help!

[rylo]

Hi

Try this

Please Login or Register  to view this content.

rylo

[hluk]

hi rylo, thanks for the help!

I tried running the macro and nothing is being recorded in the column AB of "data".

I think the macro is working though otherwise?

[rylo]

Hi

Sorry the line

Please Login or Register  to view this content.

should be

Please Login or Register  to view this content.

rylo

[hluk]

oh thanks rylo!!!

last thing. is there a simple line we can add to check in each row if "AB" has a value already or not?

if there is a value, skip to next line

if there is no value, perform the macro

- i love these forums!

[rylo]

Hi

Please Login or Register  to view this content.

rylo

[hluk]

thanks again for the reply!

is there anyway to prevent it from looping through altogether if there is a value?

the reason I ask is... when i have a lot of data, running the macro through the sheet could take a long time if each row is looped through.

thus, if there is already a value, we can skip the row to save on computing time/load.

thanks for you help thus far. don't worry about this if this is getting too complicated...

THANKS!

[rylo]

Hi

Sure, just put the test on the length of R10 as the first action of the loop. If it is >0, then do the actions, otherwise move on.

rylo

[hluk]

thanks rylo!

i had to change it from "If it is >0, then do the actions, otherwise move on"

to "If it is =0, then do the actions, otherwise move on."

same concept thought

here is the final code that is working for me for anyone else interested:

Please Login or Register  to view this content.

you see anything wrong with this rylo? thanks!

[rylo]

Hi

D'oh. Yea got the test back to front. Looks OK.

rylo

[hluk]

thanks buddy! marked as solved.

anything i can do to UP your rep?

[hluk]

figured out how to +1 rep

just did it!