Macro add row

[dennisli]

My current worksheet covers year 2000 to 2004, as the following table shows. Now I want to add one row under the row containing 2004, no matter which column contain 2004. In the added row just under the cell containing 2004, enter 2005.
This is just for illustration. In my worksheet, there are hundreds of this kind situation. Right now, I just enter by manual and feel painful.
Can you experts give me a help for the Macro code?
Thanks lot.
Dennis

col A col H
2000 2000
2001 2001
2002 2002
2003 2003
2004 2004



2000
2001
2002
2003
2004

 

[sweater_vests_rock]

dennisli.

Try the code below on a test sample. I'm not entirely sure of what you mean by "Now I want to add one row under the row containing 2004, no matter which column contain 2004."

Sub Insert2005()

Dim rngAddressA As String, rngAddressH As String
Dim SearchVal As Integer, ReplaceVal As Integer
Dim c As Range, rw as Range

SearchVal = 2004
ReplaceVal = 2005
rngAddressA = "A1:" & Range("A:A").SpecialCells(xlCellTypeLastCell).Address
rngAddressH = "H1:" & Range("H:H").SpecialCells(xlCellTypeLastCell).Address

With Union(Range(rngAddressA), Range(rngAddressH))
    Set c = .Find(SearchVal, LookIn:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            Set rw = c.Offset(rowOffset:=1, columnOffset:=0)
            rw.Insert Shift:=xlDown
            c.Offset(rowOffset:=1, columnOffset:=0) = ReplaceVal
            Set c = .FindNext(c)
            
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With
            
End Sub

Hope that Helps. Ben.

 

[dennisli]

Ben,
Thanks lot. It works well.
But there is another problem. I mean, maybe column A contains year 2004, column H contains year 2004, or any other columns may also contain year 2004.
That is to say, your code is only limited to column A and H, could you expand your code to any column?
Thanks in advance.
Dennis

 

[sweater_vests_rock]

Dennis.

I think one of the below should do the trick.

Ben.

-----

If all of your columns and rows are contiguous (by this I mean that you do not have an entirely blank column or row segmenting your data), you can use this:

Sub Insert2005()

Dim SearchVal As Integer, ReplaceVal As Integer
Dim c As Range, rw As Range, cStart As Range, rngTotal As Range

SearchVal = 2004
ReplaceVal = 2005
Set cStart = Range("A1") 'This defines the starting point of your search.
Set rngTotal = cStart.CurrentRegion

With rngTotal
    Set c = .Find(SearchVal, LookIn:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            Set rw = c.Offset(rowOffset:=1, columnOffset:=0)
            rw.Insert Shift:=xlDown
            c.Offset(rowOffset:=1, columnOffset:=0) = ReplaceVal
            Set c = .FindNext(c)
            
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With
            
End Sub

If you DO have non-contiguous ranges, then use this:

Sub Insert2005()

Dim SearchVal As Integer, ReplaceVal As Integer
Dim c As Range, rw As Range, rngTotal As Range

SearchVal = 2004
ReplaceVal = 2005
Set rngTotal = Cells

With rngTotal
    Set c = .Find(SearchVal, LookIn:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            Set rw = c.Offset(rowOffset:=1, columnOffset:=0)
            rw.Insert Shift:=xlDown
            c.Offset(rowOffset:=1, columnOffset:=0) = ReplaceVal
            Set c = .FindNext(c)
            
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With
            
End Sub