For step loop, need step size to change based on reference cell in row

**Telperion** · 12-27-2012, 06:05 PM

Goal: To take each row of source data with sequence number on one sheet and copy it to every other row on another sheet. When loop meets a row where the sequence number is the same as the previous row, the row should be copied directly below the previous row. When it reaches the next unique sequence number, it should leave a blank row before continuing.

What's working: I currently am using a For Step loop and a helper column in the source data. I can get my source sheet to copy to another worksheet every other row.

Problem: I don't know how to tackle the part where it pastes consecutive rows when the sequence number is the same, then starts up again with every other row. If for example the loop reaches J11 and J10 had the same sequence (001, 015, 205, etc), it copies differently.

My code can be found below, I leave a lot of comments in my code otherwise I forget in 2 months why it's working the way it does when I go to maintain it. Currently I'm not copying rows over, I'm copying 8 separate fields over which then are eventually exported as a CSV, which is why copy code might look more complicated than if they were just rows.

Please Login or Register  to view this content.

**Telperion** · 12-31-2012, 10:43 AM

(Friendly bump to the top)

I read through the forum rules and didn't see bumps as prohibited, if I missed it though just let me know.

**6StringJazzer** · 12-31-2012, 12:12 PM

Welcome to the Forum!

A bump now and then is fine as long as a reasonable amount of time has passed. There are no explicit rules about it but a bump after an hour would be frowned upon.

I am having a hard time reconciling your description with your code. Your description refers to sequence numbers but I can't see any reference to sequence numbers in your code. Your description talks about copying to every other row, but I can't tell that from the code. Maybe that's what your helper column does. If the only reason for your helper column is to support this code, then you can just do it all in code and remove the helper column.

Bottom line is that it's hard to understand the code without seeing your data. Can you attach the file?

Also I find it unusual that you add 0 to everything. This is sometimes done in worksheet formulas to force a conversion from text to numeric, but VBA generally does those types of conversions automatically based on context.

**Telperion** · 12-31-2012, 12:57 PM

The mostly likely reason for unnecessary (0 + *) references is that I reused some old code for this project, so it might not be needed.

I've attached the worksheet with where I'm at with it right now, and created a test button to show the current functionality. I didn't want to bog down the original post with the intricate details of the full process of what I'm trying to do, but I'll describe it here so anyone can get a full picture of what I'm trying to do.

I work in payment processing, and there's a certain alternate process flow I need this for. The Excel sheet generates a CSV payment file to be posted in our payment system. More specifically, it merges an existing output file of check data with account data that is entered in this spreadsheet. I've attached a dummy file with sample layout of the file I need to generate. All the lines that begin 0100 are account numbers generated by the Excel sheet, all the lines beginning 9999 are check data generated by another program. The spreadsheet imports a text file of check data (9999 rows) and merges all the 0100 (account number) records with the 9999 records, then outputs a merged text file which I then post.

With simple alternating rows, it's easy, because I know all 0100 records are on odd rows and all 9999 records are on even rows. I have a version of this that works fully right now as-is, but I need it to be intelligent enough to properly copy over instances where there are, for example, 3 account numbers being paid by one check. This is what I show in the text file.

I know this is very complicated to try to explain, I'm trying to hit the core points of what's relevant while leaving out the useless information. I plan on starting a new thread for a separate piece of code later on, so as to not muddy up this thread with multiple problems I have.

Thanks in advance for any help!

**6StringJazzer** · 01-08-2013, 10:45 PM

I'm getting this but I am looking at your spreadsheet and do not see anything labeled as a check number or account number. It would also help to understand what the data looks like that is the check data generated by another program and how it is imported into your Excel file. It is a straightforward matter to match up account numbers or check numbers between the two data sources, but I can't make out how the data is organized.

**Telperion** · 01-22-2013, 03:41 PM

I got busy with some other things at work and took a break from this, then had a spark of inspiration and coded up a much more elegant solution than what I had been trying to do before. For the sake of clarity and helping anybody else trying to do something remotely similar, I've attached a snippet of my worksheet with relevant code, with anything remotely considered "sensitive" of course I created as dummy data.

The data on the sheet "Import" is what I receive as output check information, I've labeled the columns for ease of understanding. Step through the CopyParcels macro for the process. If anybody sees any optimizations, of course let me know.