Sorting Data - Inserting rows into a very specific place within a table?

[S7ewie]

The people on here have been fantastic with helping me out in the past. This time however I'm returning with a slightly more complicated problem.

The big issue is that my brain can't work out how to solve my problem. I could probably work out the code if I could just work out what I needed it to do. But my knowledge of the scope of VBA is limited here so i need some ideas from brighter minds.

This is pretty complicated so please bare with me. I've tried to simplify things on here in the past but that usually just leads to issues so I'll try to be a thorough as I can. I've included a file, I recommend having that open whilst you read through this.

Background:

I work in agricultural R&D which involves looking at field trials. These are typically square or rectangular shaped blocks of plots of a certain crop (wheat for example). A standard trial will have, for example, 10 treatments (different varieties) and 1 or 3 reps (replicates). Subsequent reps will include the same treatments as rep 1 but their placement will be randomized within that rep.

We use various pieces of software to score, track and analyze data throughout the season. Some of the equipment requires a simple .csv map of the trial using "Range" and "Row" as horizontal and vertical co-ordinates (if looking from above). Try not to get "Range" and "Row" mixed up with excel terms (see the "Map" in the file to see how this translates). We can use our main trial management software to output this, then we change a few headers and adjust things in excel and save it as a .csv

I'm trying to automate this process as it can be time consuming to do it for many hundreds of trials.

The Problem:

The problem is that, when we're limited on the width of space we have, sometimes we have to "wrap" a trial and make it deeper. This is fine if there is an even number of treatments, when its odd however, this creates a blank plot (see the "Map" in the file). Annoyingly our management software wont output these plots, it leaves a blank line (as you should see from the table in the file). And unfortunately, the other software we use doesn't understand a trial that isn't square, so this needs to be added manually which can be very time consuming and has room for a lot of error which can cause massive hold ups during harvest.

What I want to do:

I need a way to insert those lines into a trial and format them appropriately.

My current code works fine with 1 rep (I'll use this tab as an example). It works out the highest number in the "Row" column (5), the highest "Range" (2) and the "Plot" count (9). It then works out the range difference (rangeDiff (1) = highestNo (5) * rangeCount (2) - plotCount (9)) and assumed that if the rangeDiff doesn't equal zero, then there are missing columns (there might be better ways of doing this but this was the best I could come up with). The rangeDiff is also the amount of rows that would need to be added. My current code just autofills down by the rangeDiff and fills in the blanks with a "G" (for Guard plot).

This works fine for 1 rep as it can simply add it to the bottom but the issue is with multiple reps as those rows would need to be inserted at specific spots within the table.

I've tried to think of how I could do it by sorting the table in various ways by I'm struggling to work out how I an get it work out which "Ranges" are missing a row, add them, and then autofill them appropriately.

Maybe I can get it to count the number of rows in each "Range", compare it to the highestNo, then get it to insert the difference in rows?

Maybe there's an easy way of doing this or maybe its going to be a pain in the backside however I look at it but hopefully someone can help me out.

Sorry for the huge load of information. I wanted to be thorough as I've been told off before for not being thorough enough in the past haha. I've included my code below but please see the file as the extra info I've added should make it easier to understand (the Map and the variables wont be on the final files).

Thanks

S7ewie

Please Login or Register  to view this content.

[S7ewie]

Update:

I think I've worked out what I need to do. Something like..

Dim rangeNo as Long

rangeNo = 1

Then I need something like:

For each rangeNo between 1 and rangeCount

count all cells that contain rangeNo.value in column A down to lastRow

If Not = highestNo

insert the difference in rows

autofill down

End if

rangeNo = rangeNo + 1

Next

Obviously that's all wrong but I think that's what I need to work out how to do?