Find column and then insert column
 

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

Find column and then insert column
 

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


"PAG" wrote:

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

Find column and then insert column
 

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

"PAG" wrote:

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

Find column and then insert column
 

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"


"PAG" wrote:

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

"PAG" wrote:

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

Find column and then insert column
 

That worked Joel - Thank you so much

"Joel" wrote:

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"


"PAG" wrote:

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

"PAG" wrote:

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