Find column and then insert column

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

PAG - 30 Nov 2007 18:11 GMT

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

Reply to this Message

Joel - 30 Nov 2007 19:06 GMT

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,
>
[quoted text clipped - 15 lines]
>
> Peter

Reply to this Message

PAG - 30 Nov 2007 20:07 GMT

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

> Hi,
>
[quoted text clipped - 15 lines]
>
> Peter

Reply to this Message

Joel - 30 Nov 2007 20:10 GMT

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"

> Hi Joel,
>
[quoted text clipped - 33 lines]
> >
> > Peter

Reply to this Message

PAG - 30 Nov 2007 20:15 GMT

That worked Joel - Thank you so much

> I wasn't sure after the column is added which column C would refer to.
>
[quoted text clipped - 40 lines]
> > >
> > > Peter

Reply to this Message