How can avoid .copy and .paste in this loop?

I wrote a code that creates two (2) lines of calculations and a delimiter between both sections.
The first iteration looks like this:
1.1
delim
1.2
The second iteration looks like this:
1.1
2.1
delim
1.2
2.2
and so forth.

I posted photos for a visual representation.

My code is below & I want to avoid using .copy, .paste, but when I tried to use an array, the values didn't store because the range changed. I spent four hours on my pre-made solution, but I'm sure there is a much easier way to do precisely what I'm doing with fewer lines of code.

Thanks in advance.

Please Login or Register  to view this content.

Please attach your actual Excel file so we can run your code. Please see "HOW TO ATTACH YOUR SAMPLE WORKBOOK" in the yellow banner at the top of the page and attach your actual file.

By the way, your code would be much more readable if you used indentation to show control structure. Here is your code unchanged except for indentation:

Please Login or Register  to view this content.

This is very complicated code so I'm going to have to just focus on the specifics of your question. I do not even know the procedure to run this code, since it appears that a bunch of data setup is required before this sub runs.

Why do you want to avoid copy and paste? You said it might be done in fewer lines of code, but fewer is not always better. Also, it's only two lines of code, so the best we can do is one:

Please Login or Register  to view this content.

or this

Please Login or Register  to view this content.

But if your code produces the desired results I wouldn't touch it.




I do feel obligated to point out a couple of things.

There are a large number of global variables in ASCE_Equipment. They can be used judiciously but can also wreak havoc if you are trying to diagnose a bug. This is called common environment coupling and is generally considered undesirable. The best practice is to pass variables as arguments to subs, rather than making them all global.

There are undeclared variables. I strongly recommend to everyone that they use Option Explicit and declare variables. Doing so prevents a lot of bugs and runtime errors.

There are frequent uses of deeply nested If/Else statements. In these cases, you can use the ElseIf structure to avoid the nesting. This is not a bug, but it is a tighter coding technique that is more readable and less prone to error. Here is a section of Sub variables rewritten:

Please Login or Register  to view this content.

If you use Copy and then PasteSpecial you will get the "marching ants" on the "from" range and you can you

Please Login or Register  to view this content.

to turn it off after the paste.

The line you quoted should copy the whole range. Also, it just copies the values, not formats, but that might be OK for your situation. And as jolivanes suggests, you can learn a lot by trying stuff out.