I have been searching the internet and watching Youtube videos for days trying to create a macro in VBA that will enter a row above a specific text string in a worksheet in Excel. Every worksheet I would perform this macro on has the term "Actual Earnings" somewhere between cells A19 and A22. What I would like for the macro to do is find the Text "Actual Earnings" and insert rows above the text depending on where it falls in the spreadsheet. If it falls in cell A22, I don't want it to do anything. If it falls in A21, I want it to enter 1 row. If it falls in A20, I want it to enter two rows and if it falls into A19 I want it to enter 3. I'm trying to make each worksheet in my workbook uniform with the row "Actual Earnings" always falling in cell A22. Can anyone help me formulate this Macro? I found one that will enter a row but it enters the row above where I have selected a cell in the workbook as opposed to entering it above the text string and I'm not advanced enough to understand how to do everything I want it to. I would also like for it to work without a command button. Here is what I have to start with, I don't even know if it'll work for what I'm trying to do. Any help would be greatly appreciated!!
Sub InsertRow()
Dim Found As Range
Set Found = Columns("A").EntireRow.Find(what:="Actual:", LookIn:=xlValues, Lookat:=xlWhole)
If Not Found Is Nothing Then Found.Select
Rows(Selection.Row).Insert Shift:=xlDown
End Sub
Sub InsertRow()
Dim Found As Range
Set Found = Columns("A").EntireRow.Find(what:="Actual:", LookIn:=xlValues, Lookat:=xlWhole)
If Not Found Is Nothing Then Found.Select
Rows(Selection.Row).Insert Shift:=xlDown
End Sub
You should avoid merged cells if at all possible because they cause havoc for Excel macros. Do some research into "Centre across selection". It can most often achieve the same results as merging cells with the problems.
