macro to insert row and type data

[LM]

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.

 

[Don Guillett]

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

 

[Mike H]

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.