I need help building a loop in Excel-VBA

Question

I have a block of data like below that I need to insert into a sheet with several thousand rows.

The first catch is that some of the block is already there but incomplete. This is best illustrated with examples.

In this case, I am missing rows for 7010 and 7020.

In this case, I am only missing 7010. It’s all random. I could override the existing block but the second catch is that I have data in the cells to the right so the obvious solution is to insert blank rows corresponding to the missing numbers.

Because I know the block always start with a 7, I started doing something like (the data is in column B)

If Left(Cells(Row, 2), 1) = 7 Then
If Cells(Row, 2) = 7005 Then

Then realized that’s a quagmire of nested if statements. And then it gets trickier because after I insert the necessary row, the cycle repeats XD so I thought maybe I use an array to store the incomplete block and compare it to the complete block then insert rows that way but that’s also a nightmare. This is all made worse because I have to loop through the entire sheet and do this wherever necessary. I can code so if anyone can just provide logic, that will suffice.

If it’s easier, I do not need to fill in the newly inserted rows. Meaning I can just leave it all blank like below. Let me know if this makes sense or need more info.

Don't ask why I am doing this. I know it's obtuse but I don't have a choice XD — findwindow, Jul 30 '15 at 17:06
Actually it is pretty simple :) `1` Loop through the first block and check if it is there or not in the 2nd block. `2` If it is not then simply add it to the end of the block `3` Sort the 2nd complete block based on column 1 in the end. The blank rows on the right of the numbers will be automatically created :) — Siddharth Rout, Jul 30 '15 at 17:15
Whoa Sid. The man that started my SO journey! I feel honored ^_^; That's not bad at all. Figure out what's missing, add it then worry about order last. Will try that... — findwindow, Jul 30 '15 at 17:18
Hmmm what's the best way to do step 1? Block 2 doesn't always start with 7005. This is still very tedious XD — findwindow, Jul 30 '15 at 17:37
You will have to loop through the first block and then either use `.Find` or `Application.WorksheetFunction.CountIf` to check for duplicates — Siddharth Rout, Jul 30 '15 at 17:43
There's multiple approaches from there. Best is to append missing number as soon as it's not found instead of keeping an array to store them? Then it's a pain to keep track when original block ends? Uhh, wanna code this for me =P — findwindow, Jul 30 '15 at 17:48
I might just do this manually XD 5000 lines, 200-300 blocks I need to fix... an hour at most. This will take me days to code ;_; — findwindow, Jul 30 '15 at 17:52
It's very simple. You have done more complex coding :) Perhaps [this](http://www.siddharthrout.com/2011/07/14/find-and-findnext-in-excel-vba/) may get you started. — Siddharth Rout, Jul 30 '15 at 17:55
More complex yes but not as tedious XD On somewhat of a time crunch so no time to study find/findnext :/ It's funny. A human can still be quicker than machine for several thousand lines in SOME cases ^_^ Please post your first comment as answer so I can mark it as closed? Finally, thank you for dropping by <3 Learned so much from you and look forward to continue doing so! — findwindow, Jul 30 '15 at 18:01
I will do better than that. i will not completely answer your question yet i will post enough to get you SPRINTING!!! — Siddharth Rout, Jul 30 '15 at 18:06
Ah, but I bet I can do this quicker manually than you coding =P The logic here is rather cumbersome.. at least for me :/ — findwindow, Jul 30 '15 at 18:09

score 1 · Accepted Answer · edited May 23 '17 at 11:51

Actually it is pretty simple :)

Let me give you a hint with code examples which will not directly answer your question but will get you started!

Note: I am using hardcoded range for demonstration purpose.

Loop through the first block and check if it is there or not in the 2nd block.

Example

Dim BlockA As Range, BlockB As Range
Dim aCell As Range

Set BlockA = Range("M1:M10")
Set BlockB = Range("A1:A100")

For Each aCell In BlockA
    If Application.WorksheetFunction.CountIf(BlockB, aCell.Value) > 0 Then
        Debug.Print "Match Found for " & aCell.Value
    Else
        Debug.Print "Match Not Found for " & aCell.Value
    End If
Next

If it is not then simply add it to the end of the block

First find the last row using THIS and then simply increment it before writing to it.

Example

LastRow = Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp).Row + 1
'~~> aCell is the cell which is not there in the 2nd block
Range("A" & LastRow).Value = aCell.Value

Sort the 2nd complete block based on column 1 in the end. The blank rows on the right of the numbers will be automatically created :)

Example

Let's say your range is from A1:F100 then you can use this for sorting

With Sheet1
    .Columns("A:F").Sort Key1:=.Range("A2"), _
                         Order1:=xlAscending, _
                         Header:=xlYes, _
                         OrderCustom:=1, _
                         MatchCase:=False, _
                         Orientation:=xlTopToBottom, _
                         DataOption1:=xlSortNormal
End With

Gah you beat me. Kind of. I apologize. I was not clear. There are 4743 rows while each blockB only ranges from 5 to 100ish. So I don't want the lastrow of the sheet but lastrow of the current block. The sheet is also already sorted so when I sort, I need to only sort the current block as well. Although I could do a mass sort with multiple criteria at the very end. Still +1 and will DEFINITELY do this next year! — findwindow, Jul 30 '15 at 18:46

I need help building a loop in Excel-VBA

1 Answers1