Need help with VBA

airbornflght

I'm not sure how many people on here are familiar with this bastation of a language. But I need help.

What I have is an excel with lets 15 columns and around 950 rows currently. One of the columns is a number. I have them sorted ascending right now, but the problem is that some numbers are missing. The list may go

xx xx xx 1 xx
xx xx xx 2 xx
xx xx xx 3 xx
xx xx xx 7 xx
xx xx xx 8 xx
xx xx xx 12 xx
xx xx xx 13 xx
xx xx xx 15 xx

What I want to do is find that skipped row, insert a blank row where it should be and set the value of that cell to the number that it should be.

So that the list will be:

xx xx xx 1 xx
xx xx xx 2 xx
xx xx xx 3 xx
xx xx xx 4 xx
xx xx xx 5 xx
xx xx xx 6 xx
xx xx xx 7 xx
xx xx xx 8 xx
xx xx xx 9 xx
xx xx xx 10 xx
xx xx xx 11 xx
xx xx xx 12 xx
xx xx xx 13 xx
xx xx xx 14 xx
xx xx xx 15 xx

The code that I made so far is part correct part psuedo code. I don't know vba so it may not be the most elegant way to do the job.

Sub insertRow()

Dim chaptNum As Integer
Dim maxChapt As Integer
Dim count As Integer

maxChapt = 1224
chaptNum = 1

Do While chaptNum < maxChapt

    If cell.value != chaptNum Then
        Selection.EntireRow.Insert
        Selection.Value = chaptNum
        count = count + 1
    End If

    chaptNum = chaptNum + 1
Loop

MsgBox "The loop finished and made " & count & "additions."

End Sub

Garg

I don't know the Excel classes, but here are my thoughts. Where is cell set? Seems like the cell object should be set to the selected cell. You'll need to set your cell object = the currently selected cell each time before running the If statement (unless cell is some kind of special object that always equals the selected cell).

The other thing I wonder about is how you're selecting the next row each time. It should be outside the If block so that the selection is updated each time. Does Selection.Value select a cell (or cells) with that value? It might be more robust to select the cell based on the row/column coordinates, if Excel lets you do that. You know what column the cell will always be in, and you can use that incremented row value chaptNum for the row.

kryyst

You could do this quickly the manual mode. Add a 16th column that has every number you need do a vlookup to combine the first 15th colums with their correct 16th column which will now give you a 17th colum that looks like

xx xx xx 1 xx
xx xx xx 2 xx
-- -- -- 3 --
-- -- -- 4 --
xx xx xx 5 xx

Then you just turn the vlookup formula results back into a constant using a copy and replace special and your done.

airbornflght

most of what I wrote was pseudo code. Only to give someone who knew vba the gist of what I wanted it to do.