Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to insert row and type data
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to insert row and type data
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 "LM" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to insert row and type data
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA Import Convert Data Type from Type 1 to Type 2 | Excel Programming | |||
Macro To Insert Row And Add Data. | Excel Programming | |||
Macro To Find Data And Insert Row | Excel Programming | |||
create macro to move label type data to column data | Excel Programming | |||
UDF, Type it or use Insert Function | Excel Programming |