I have a large spread containing historical information that i update
monthly. I am trying to write a macro that will go to the last month updated
and insert a row beneath it copying the formulas from above. The date is in
column A. Any suggestoins?

Sub ABC()
Dim rng As Range
Set rng = Rows(Cells(Rows.Count, 1).End(xlUp)(2).Row)
rng.FillDown
On Error Resume Next
rng.SpecialCells(xlConstants).ClearContents
On Error GoTo 0
End Sub

--
Regards,
Tom Ogilvy




"John" wrote in message
...
I have a large spread containing historical information that i update
monthly. I am trying to write a macro that will go to the last month
updated
and insert a row beneath it copying the formulas from above. The date is
in
column A. Any suggestoins?

Tom,

Thanks for the help. I don't think I gave you enough information. The
spread sheet contains blank lines. I was hoping the macro would seek out the
last date updated in column A and add a line after that. Sorry for the
confustion. See below for a condensed example of what the spread sheet looks
like

Well A

Company Name

Various Info
Various Info

May-05 1234 1234 Formula 1234
June-05 1234 1234 Formula 1234
July-05 1234 1234 Formula 1234


"Tom Ogilvy" wrote:

Sub ABC()
Dim rng As Range
Set rng = Rows(Cells(Rows.Count, 1).End(xlUp)(2).Row)
rng.FillDown
On Error Resume Next
rng.SpecialCells(xlConstants).ClearContents
On Error GoTo 0
End Sub

--
Regards,
Tom Ogilvy




"John" wrote in message
...
I have a large spread containing historical information that i update
monthly. I am trying to write a macro that will go to the last month
updated
and insert a row beneath it copying the formulas from above. The date is
in
column A. Any suggestoins?

That's what it does assuming the last date updated is the last filled cell
in column A (which is what I understood you to say).
--
Regards,
Tom Ogilvy

"John" wrote in message
...
Tom,

Thanks for the help. I don't think I gave you enough information. The
spread sheet contains blank lines. I was hoping the macro would seek out
the
last date updated in column A and add a line after that. Sorry for the
confustion. See below for a condensed example of what the spread sheet
looks
like

Well A

Company Name

Various Info
Various Info

May-05 1234 1234 Formula 1234
June-05 1234 1234 Formula 1234
July-05 1234 1234 Formula 1234


"Tom Ogilvy" wrote:

Sub ABC()
Dim rng As Range
Set rng = Rows(Cells(Rows.Count, 1).End(xlUp)(2).Row)
rng.FillDown
On Error Resume Next
rng.SpecialCells(xlConstants).ClearContents
On Error GoTo 0
End Sub

--
Regards,
Tom Ogilvy




"John" wrote in message
...
I have a large spread containing historical information that i update
monthly. I am trying to write a macro that will go to the last month
updated
and insert a row beneath it copying the formulas from above. The date
is
in
column A. Any suggestoins?

Tom,

Thanks for all the help. Possibly the problem I am having is because there
are several wells that i am tracking on one spread sheet. I played around
with the spread sheet and got it to add a row like it should for the last
well however i need it to add a row for each well. See Below:

Well A

May
Jun
Jul

Well B

May
Jun
Jul

Sorry I didn't explain the problem better.

"Tom Ogilvy" wrote:

That's what it does assuming the last date updated is the last filled cell
in column A (which is what I understood you to say).
--
Regards,
Tom Ogilvy

"John" wrote in message
...
Tom,

Thanks for the help. I don't think I gave you enough information. The
spread sheet contains blank lines. I was hoping the macro would seek out
the
last date updated in column A and add a line after that. Sorry for the
confustion. See below for a condensed example of what the spread sheet
looks
like

Well A

Company Name

Various Info
Various Info

May-05 1234 1234 Formula 1234
June-05 1234 1234 Formula 1234
July-05 1234 1234 Formula 1234


"Tom Ogilvy" wrote:

Sub ABC()
Dim rng As Range
Set rng = Rows(Cells(Rows.Count, 1).End(xlUp)(2).Row)
rng.FillDown
On Error Resume Next
rng.SpecialCells(xlConstants).ClearContents
On Error GoTo 0
End Sub

--
Regards,
Tom Ogilvy




"John" wrote in message
...
I have a large spread containing historical information that i update
monthly. I am trying to write a macro that will go to the last month
updated
and insert a row beneath it copying the formulas from above. The date
is
in
column A. Any suggestoins?

John,

You could use a multiple find:

Sub Find_Multiple()
With Worksheets(1).Range("a1:a500")
Set c = .Find("Well", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
MsgBox "Found Well"
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
End Sub

And in each loop you could do the equivalent of Ctrl + DownArrow and
Ctrl + DownArrow. Than you want to insert a row and selection.Offset(1,0)

You can get the second part of the code using the macro recorder and
than modifying the macro to be more general.

Post back and let us know if this helps...


--
steveB

Remove "AYN" from email to respond
"John" wrote in message
...
Tom,

Thanks for all the help. Possibly the problem I am having is because
there
are several wells that i am tracking on one spread sheet. I played around
with the spread sheet and got it to add a row like it should for the last
well however i need it to add a row for each well. See Below:

Well A

May
Jun
Jul

Well B

May
Jun
Jul

Sorry I didn't explain the problem better.

"Tom Ogilvy" wrote:

That's what it does assuming the last date updated is the last filled
cell
in column A (which is what I understood you to say).
--
Regards,
Tom Ogilvy

"John" wrote in message
...
Tom,

Thanks for the help. I don't think I gave you enough information. The
spread sheet contains blank lines. I was hoping the macro would seek
out
the
last date updated in column A and add a line after that. Sorry for the
confustion. See below for a condensed example of what the spread sheet
looks
like

Well A

Company Name

Various Info
Various Info

May-05 1234 1234 Formula 1234
June-05 1234 1234 Formula 1234
July-05 1234 1234 Formula 1234


"Tom Ogilvy" wrote:

Sub ABC()
Dim rng As Range
Set rng = Rows(Cells(Rows.Count, 1).End(xlUp)(2).Row)
rng.FillDown
On Error Resume Next
rng.SpecialCells(xlConstants).ClearContents
On Error GoTo 0
End Sub

--
Regards,
Tom Ogilvy




"John" wrote in message
...
I have a large spread containing historical information that i
update
monthly. I am trying to write a macro that will go to the last
month
updated
and insert a row beneath it copying the formulas from above. The
date
is
in
column A. Any suggestoins?

Steve,

That works great for finding a specific date which will always be in Column
A. I tried to combine your and Tom's code so every time it finds the
specified date it adds a row however i am drawing blanks on how to get this
to work. Any ideas on what I'm missing?

Dim rng As Range


Sub Find_Multiple()
With Worksheets(1).Range("a1:a5000")
Set c = .Find("Jul-05", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Set rng = Rows(Cells(Rows.Count, 1).End(xlUp)(2).Row)
rng.FillDown
On Error Resume Next
rng.SpecialCells(xlConstants).ClearContents
On Error GoTo 0
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
End Sub

Thanks again,

John

"STEVE BELL" wrote:

John,

You could use a multiple find:

Sub Find_Multiple()
With Worksheets(1).Range("a1:a500")
Set c = .Find("Well", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
MsgBox "Found Well"
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
End Sub

And in each loop you could do the equivalent of Ctrl + DownArrow and
Ctrl + DownArrow. Than you want to insert a row and selection.Offset(1,0)

You can get the second part of the code using the macro recorder and
than modifying the macro to be more general.

Post back and let us know if this helps...


--
steveB

Remove "AYN" from email to respond
"John" wrote in message
...
Tom,

Thanks for all the help. Possibly the problem I am having is because
there
are several wells that i am tracking on one spread sheet. I played around
with the spread sheet and got it to add a row like it should for the last
well however i need it to add a row for each well. See Below:

Well A

May
Jun
Jul

Well B

May
Jun
Jul

Sorry I didn't explain the problem better.

"Tom Ogilvy" wrote:

That's what it does assuming the last date updated is the last filled
cell
in column A (which is what I understood you to say).
--
Regards,
Tom Ogilvy

"John" wrote in message
...
Tom,

Thanks for the help. I don't think I gave you enough information. The
spread sheet contains blank lines. I was hoping the macro would seek
out
the
last date updated in column A and add a line after that. Sorry for the
confustion. See below for a condensed example of what the spread sheet
looks
like

Well A

Company Name

Various Info
Various Info

May-05 1234 1234 Formula 1234
June-05 1234 1234 Formula 1234
July-05 1234 1234 Formula 1234


"Tom Ogilvy" wrote:

Sub ABC()
Dim rng As Range
Set rng = Rows(Cells(Rows.Count, 1).End(xlUp)(2).Row)
rng.FillDown
On Error Resume Next
rng.SpecialCells(xlConstants).ClearContents
On Error GoTo 0
End Sub

--
Regards,
Tom Ogilvy




"John" wrote in message
...
I have a large spread containing historical information that i
update
monthly. I am trying to write a macro that will go to the last
month
updated
and insert a row beneath it copying the formulas from above. The
date
is
in
column A. Any suggestoins?

John,

I thought you wanted to add a row to each "collection" (well)?

Again the trick is to find the header for each Well and than find the last
row in that section.

What I need to know is:
What do the Well headers look like.
The data that goes into the well.
The number of blank lines in this data.

From your examples in your first post it looked like you had 2 segments of
data in each well.
You appeared to have "Well x" as the header, but now you appear to be
looking for dates. No biggie...

Your code appears to just create a new header...

--
steveB

Remove "AYN" from email to respond
"John" wrote in message
...
Steve,

That works great for finding a specific date which will always be in
Column
A. I tried to combine your and Tom's code so every time it finds the
specified date it adds a row however i am drawing blanks on how to get
this
to work. Any ideas on what I'm missing?

Dim rng As Range


Sub Find_Multiple()
With Worksheets(1).Range("a1:a5000")
Set c = .Find("Jul-05", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Set rng = Rows(Cells(Rows.Count, 1).End(xlUp)(2).Row)
rng.FillDown
On Error Resume Next
rng.SpecialCells(xlConstants).ClearContents
On Error GoTo 0
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
End Sub

Thanks again,

John

"STEVE BELL" wrote:

John,

You could use a multiple find:

Sub Find_Multiple()
With Worksheets(1).Range("a1:a500")
Set c = .Find("Well", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
MsgBox "Found Well"
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
End Sub

And in each loop you could do the equivalent of Ctrl + DownArrow
and
Ctrl + DownArrow. Than you want to insert a row and
selection.Offset(1,0)

You can get the second part of the code using the macro recorder
and
than modifying the macro to be more general.

Post back and let us know if this helps...


--
steveB

Remove "AYN" from email to respond
"John" wrote in message
...
Tom,

Thanks for all the help. Possibly the problem I am having is because
there
are several wells that i am tracking on one spread sheet. I played
around
with the spread sheet and got it to add a row like it should for the
last
well however i need it to add a row for each well. See Below:

Well A

May
Jun
Jul

Well B

May
Jun
Jul

Sorry I didn't explain the problem better.

"Tom Ogilvy" wrote:

That's what it does assuming the last date updated is the last filled
cell
in column A (which is what I understood you to say).
--
Regards,
Tom Ogilvy

"John" wrote in message
...
Tom,

Thanks for the help. I don't think I gave you enough information.
The
spread sheet contains blank lines. I was hoping the macro would
seek
out
the
last date updated in column A and add a line after that. Sorry for
the
confustion. See below for a condensed example of what the spread
sheet
looks
like

Well A

Company Name

Various Info
Various Info

May-05 1234 1234 Formula 1234
June-05 1234 1234 Formula 1234
July-05 1234 1234 Formula 1234


"Tom Ogilvy" wrote:

Sub ABC()
Dim rng As Range
Set rng = Rows(Cells(Rows.Count, 1).End(xlUp)(2).Row)
rng.FillDown
On Error Resume Next
rng.SpecialCells(xlConstants).ClearContents
On Error GoTo 0
End Sub

--
Regards,
Tom Ogilvy




"John" wrote in message
...
I have a large spread containing historical information that i
update
monthly. I am trying to write a macro that will go to the last
month
updated
and insert a row beneath it copying the formulas from above.
The
date
is
in
column A. Any suggestoins?