I just got news that I need to add more info to a huge spreadsheet and I
really need a macro to do this for me to save time.

I have invoice #s in column L that are formatted like this:
701025467 means that
7 = 2007
01 = 1st month (January)
02 = 2nd period of January (16th thru the 31st)

612017766 means that
6 - 2006
12 - 12th month (December)
01 - 1st period of December (1st thru the 15th)

I need a visual basic code that will identify:

1) if the invoice # in column L starts with a '7' then the year "2007" needs
to be added to the same row in column O. If starts with a '6' then the year
"2006" needs to be added to the same row in column O.

2) if the invoice #'s next 2 #s in column L are '01' then the month "01"
needs to be added to the same row in the column M. If the next 2 #s are '02'
instead then the month "02" needs to added to the same row in the column M,
etc., etc. up to '12' for the 12th month.

3) if the invoice #'s 4th and 5th #s in column L are '02' then the period
"2nd" needs to added to the same row in the column N. Ff the invoice #'s 4th
and 5th #s in column L are '01' instead then the period "1st" needs to added
to the same row in the column N.

Thank you very much!

something like
for i = 2 to cells(rows.count,"L").end(xlup).row
cells(i,"o").value= "200" & left(cells(i,"L")
cells(i,"m").value=mid(cells(i,"l"),2,2)
'etc
next i

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Studebaker" <(E-Mail Removed)> wrote in message
news:E6D4ABE8-2635-4A5D-A32C-(E-Mail Removed)...
>I just got news that I need to add more info to a huge spreadsheet and I
> really need a macro to do this for me to save time.
>
> I have invoice #s in column L that are formatted like this:
> 701025467 means that
> 7 = 2007
> 01 = 1st month (January)
> 02 = 2nd period of January (16th thru the 31st)
>
> 612017766 means that
> 6 - 2006
> 12 - 12th month (December)
> 01 - 1st period of December (1st thru the 15th)
>
> I need a visual basic code that will identify:
>
> 1) if the invoice # in column L starts with a '7' then the year "2007"
> needs
> to be added to the same row in column O. If starts with a '6' then the
> year
> "2006" needs to be added to the same row in column O.
>
> 2) if the invoice #'s next 2 #s in column L are '01' then the month "01"
> needs to be added to the same row in the column M. If the next 2 #s are
> '02'
> instead then the month "02" needs to added to the same row in the column
> M,
> etc., etc. up to '12' for the 12th month.
>
> 3) if the invoice #'s 4th and 5th #s in column L are '02' then the period
> "2nd" needs to added to the same row in the column N. Ff the invoice #'s
> 4th
> and 5th #s in column L are '01' instead then the period "1st" needs to
> added
> to the same row in the column N.
>
> Thank you very much!
>
>