Prev: Copy and paste sheets to new workbook
Next: Shading cells that contain formulas and conditional formatting at thesame time.
From: LM on 7 Jun 2010 08:07
I have a worksheet that contains groups of data for different products for
each month, which I update each month. The last row in each group of data
contains a cell with the word "END" in it. I would like to use a macro to
automatically search for the word END right through the worksheet, insert a
row above the row with the word END in it and type the current month in the
cell above the word END in the new row, thus putting it below the previous
month. I don't have much experience in writing macros. I have been able to
record a macro to add the row but I can't use the record macro feature to add
the new month. Can anyone give me a simple solution please.
From: Don Guillett on 7 Jun 2010 08:31
Should do it while at the proper sheet

Option Explicit
Sub findendSAS()
Dim i As Long
Dim myfind As Range
For i = 1 To Cells.Find("*", Cells(Rows.Count, Columns.Count) _
, , , xlByColumns, xlPrevious).Column
Set myfind = Columns(i).Find(What:="End", After:=Cells(1, i), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not myfind Is Nothing Then
With Cells(myfind.Row, i)
.Insert , shift:=xlDown
.Offset(-1).Value = Format(Date, "mmm")
End With
End If
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"LM" <LM(a)discussions.microsoft.com> wrote in message
news:C57623A1-3269-4FD9-A6F5-21815ED3E62D(a)microsoft.com...
>I have a worksheet that contains groups of data for different products for
> each month, which I update each month. The last row in each group of data
> contains a cell with the word "END" in it. I would like to use a macro to
> automatically search for the word END right through the worksheet, insert
> a
> row above the row with the word END in it and type the current month in
> the
> cell above the word END in the new row, thus putting it below the previous
> month. I don't have much experience in writing macros. I have been able
> to
> record a macro to add the row but I can't use the record macro feature to
> add
> the new month. Can anyone give me a simple solution please.

From: Mike H on 7 Jun 2010 08:31
Hi,

You didn't say which column we are looking for 'END' in so change the column
in the macro if necessary.

ALT+F11 to open vb editor. Right click 'ThisWorkbook' and insert module and
paste the code in. Ensure the sheet with the data is the active sheet and run
the code

Sub insertrowifnamechg()
MyColumn = "A" 'Change to suit
For x = Cells(Rows.Count, MyColumn).End(xlUp).Row To 2 Step -1
If UCase(Cells(x, MyColumn)) = "END" Then
Rows(x).Insert
Cells(x, MyColumn) = Format(Now, "mmmm")
End If
Next x
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"LM" wrote:

> I have a worksheet that contains groups of data for different products for
> each month, which I update each month. The last row in each group of data
> contains a cell with the word "END" in it. I would like to use a macro to
> automatically search for the word END right through the worksheet, insert a
> row above the row with the word END in it and type the current month in the
> cell above the word END in the new row, thus putting it below the previous
> month. I don't have much experience in writing macros. I have been able to
> record a macro to add the row but I can't use the record macro feature to add
> the new month. Can anyone give me a simple solution please.
 | 
Pages: 1
Prev: Copy and paste sheets to new workbook
Next: Shading cells that contain formulas and conditional formatting at thesame time.