CODEWITHSUNDEEP
excelvbamacros
Paolo Medina
Paolo Medina

Reputation: 301

Insert new columns to the right after finding header

How can I insert 3 new columns to the right of the column of a specific header. Below is my initial code:

Sub InsertColumn()

    Dim DischargeDate As Range
    Set DischargeDate = Range("A1:BV1").Find("DISCHARGE DATE")
    If DischargeDate Is Nothing Then
      MsgBox "DISCHARGE DATE column was not found."
      Exit Sub
    End If
    ' If DISCHARGE DATE was found in the column "AD", insert new column on this range example:
    ' Range("AE:AG").EntireColumn.Insert

-->what should be the Range I use here since the column where DISCHARGE DATE is sometimes not in "AD"

End Sub

I hope my question is clear. Thank you!

Upvotes: 0

Views: 4328

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

Try this:

Sub InsertColumn()

    Dim DischargeDate As Range
    Set DischargeDate = Range("A1:BV1").Find("DISCHARGE DATE")
    If DischargeDate Is Nothing Then
      MsgBox "DISCHARGE DATE column was not found."
      Exit Sub
    Else
      Columns(DischargeDate.Column).Offset(, 1).Resize(, 3).Insert

    End If


End Sub

Upvotes: 2

Related Questions