VBA help needed - Row insert

[hmchang82]

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>abc</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>purchase</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>sale</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>dbc</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>purchase</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>ado</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>purchase</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>sale</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>bdi</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>sale</TD></TR></TBODY></TABLE>
Given above, I want to insert a row if there's no "sale" after purchase and insert a row if "purchase" does not exist using vba.

How do I go about doing that?

Can I use something like:

If cells(1,1).offset(2,0).value <>"sale" then
EntireRow.Insert
End If

 

[Weaver]

This appears to work for your sample data, assuming it starts in cell A1

Sub insertRowsWhereRequired()
    Dim word1 As String, word2 As String, r As Long, c As Long
    word1 = "purchase"
    word2 = "sale"
    r = 1
    c = 1
    Do While Cells(r, c) <> ""
        Select Case Cells(r, c)
            Case word1
                If Cells(r + 1, c) <> word2 Then
                    Cells(r + 1, c).EntireRow.Insert shift:=xlDown
                    Cells(r + 1, c) = word2
                End If
            Case word2
                If Cells(r - 1, c) <> word1 Then
                    Cells(r, c).EntireRow.Insert shift:=xlDown
                    Cells(r, c) = word1
                End If
        End Select
        r = r + 1
    Loop
End Sub

You can change the start row & column by altering the values of r and c at the start

HTH

 

[hmchang82]

Wow that worked perfectly!! Thank You!

 

[hmchang82]

I tried to add word "Total" after "Sale" by using below but it doesn't seem to work

Dim word1 As String, word2 As String, word3 As String, r As Long, c As Long
word1 = "Purchase"
word2 = "Sale"
word3 = "Total"
r = 1
c = 1
Do While Cells(r, c) <> ""
Select Case Cells(r, c)
Case word1
If Cells(r + 1, c) <> word2 Then
Cells(r + 1, c).EntireRow.Insert shift:=xlDown
Cells(r + 1, c) = word2
End If
Case word2
If Cells(r - 1, c) <> word1 Then
Cells(r, c).EntireRow.Insert shift:=xlDown
Cells(r, c) = word1
End If
Case word3
If Cells(r + 3, c) <> word3 Then
Cells(r + 3, c).EntireRow.Insert shift:=xlDown
Cells(r + 3, c) = word3
End If
End Select
r = r + 1
Loop

Any ideas?