For each non-empty cell.

[gshock]

Can someone help me write a simple loop? I'm writing a macro that will repeat a set of calculations for a range of data (B9:AY17). The data will not always be the same size. It may be from B9:W17, or it may be from B9:AA17.

For example, if B9 is not empty, I want to copy B9:B17 to another sheet. Then I want to repeat this process for C9, copying C9:C17 to the second sheet, repeat for D9, and so on.

Ideally, the first empty cell would stop the macro. So if H9 is empty, the macro would end.

TIA for any help you can offer.

Best regards,

-gshock

[gshock]

Hi All,

I realized that I did a poor job describing my problem so I'm adding the sample file to illustrate what I'm trying to do.

I want to take the data from Stage 1 on Sheet1, Range D5:D9, copy it to the target location on Sheet2, Range C5:C9, perform the calculations, and copy the results, Range E5:E9 back to Stage 1 on Sheet1 in the Output section, Range D15:D19.

Then I want to go to Stage 2 and repeat this process. At the first empty cell, K5, I want the macro to recognize that the cell is empty and quit the loop.

This is a very simplistic example of a MUCH larger set of calculations.

I haven't written many loops, and the few that I have used have come from some very generous individuals on this forum. I'm really struggling to get better at writing loops so any help would be GREATLY appreciated. Thanks much.

Best regards,

-gshock

[rylo]

Hi

Try this.

Please Login or Register  to view this content.

rylo

[gshock]

[/QUOTE]

Please Login or Register  to view this content.

[/QUOTE]

Hey rylo,
Thanks for the post. This code looks like it will help a lot. Thanks. I do need to make a couple of modifications so maybe you can help me understand a couple of things. In the For loop, you're eliminating the need to copy and paste, right? I am starting to understand.

What does this line of code do? I see it's taking the values from the calc sheet, but I don't understand the Resize command.

Please Login or Register  to view this content.

How do I run this code so that it recognizes an empty cell and stops?

This really helps a lot.

Best regards,

-gshock

[rylo]

Hi

Yep, removed the copy and paste by making one area's values be the same as another area's values.

Please Login or Register  to view this content.

This line of code is the same as going to the last column in row 5, then doing a ctrl left arrow to find the last filled cell in the row and then determining the column number. This effectively means you don't have to test each cell and see if it is blank and gives a defined end to the loop.

So each iteration of i is the column being actioned.

Please Login or Register  to view this content.

This makes the output range values be the same as the input range values. Using the cells approach enables you to use 2 numbers to determine the row and the column and not have to convert the column number to an alpha character (if you have that view!).

Please Login or Register  to view this content.

The resize command allows you to take a cell, and resize that single cell to a bigger range. In this case, it becomes a 5 row x 1 column block. As your output is 5 rows x 1 column, it matches in size. I've used this approach as you can use the cells command to determine the output starting point. Once you have that starting point, you expand the output range using resize and then transfer across the values.

Does that help clarify things??? Don't hesitate to ask more questions if still not clear.

rylo

[gshock]

rylo,

This helps tremendously; not just your coding suggestions, but the education as well. We do a lot of these kinds of steps, taking data from one sheet, performing calculations on it, and copying it back to an "input & output" sheet. So understanding the methodology and the syntax is very helpful.

Not that I mind asking questions, but where can I learn more of VBA coding? This site has been really helpful, but I'd really like to become very skilled at doing this kind of stuff. Any suggestions?

Thanks again,

-gshock

[gshock]

Don't hesitate to ask more questions...

rylo,
All clear, but I was just curious. Can you refer to a sheet by # (as it displays in the project window) instead of using

Please Login or Register  to view this content.

?

For example, could I simply say,

Please Login or Register  to view this content.

instead?

Thanks for taking the time.

-gshock

[royUK]

rylo,
All clear, but I was just curious. Can you refer to a sheet by # (as it displays in the project window) instead of using

Please Login or Register  to view this content.

?

For example, could I simply say,

Please Login or Register  to view this content.

instead?

Thanks for taking the time.

-gshock

Please read the Forum Rules & ask this question in it's own Thread

[gshock]

Please read the Forum Rules & ask this question in it's own Thread

royUK,

No problem. Sorry.

[royUK]

Thanks,I have replied to your new question.