Find column and then insert column

  • Thread starter Thread starter PAG
  • Start date Start date
P

PAG

Hi,

I am new to creating macros in Excel.

Can someone please help me add to a macro I currently use.

All I want to do is search an excel workbook for a particular
column(Original Notional) and have the macro add that column if it isn't
alreay there. There normally is the following columns of X-Date, Payment
Date, Fund, Sub-Portfolio, Cusip, Broker, Security Name, Notional Amt , and
Total Payment. Sometimes the workbook has the Original Notional and sometimes
it doesn't. If the Original Notional column is there it would before the
Total Payment column.

Can anyone please help me?


Thank You

Peter
 
The easiest method is to use find

with workbooks("book1.xls").sheets("Sheet1")
set c = Rows("1:1").find(what:="Original",lookin:=xlValues)
if not c is nothing then
'enter code if found
else
set c = Rows("1:1").find(what:="total",lookin:=xlValues)
if not c is nothing then
c.entirecolumn.insert
c.value = "Original"
end if
end if
end with
 
Hi Joel,

I tried the below but it inserts a new column and then renames the Total
Payment column to Original Notional.

With ActiveWorkbook
Set c = Rows("1:1").Find(what:="Original Notional", LookIn:=xlValues)
If Not c Is Nothing Then
'enter code if found
Else
Set c = Rows("1:1").Find(what:="Total Payment", LookIn:=xlValues)
If Not c Is Nothing Then
c.EntireColumn.Insert
c.Value = "Original Notional"
End If
End If
End With
 
I wasn't sure after the column is added which column C would refer to.

from
c.Value = "Original Notional"
to
c.offset(0,-1).Value = "Original Notional"
 
That worked Joel - Thank you so much

Joel said:
I wasn't sure after the column is added which column C would refer to.

from
c.Value = "Original Notional"
to
c.offset(0,-1).Value = "Original Notional"
Click to expand...
 
Back
Top