Copy, Insert, Modify Macro

[alexg7828]

Hi,

I'm trying to make a Macro but i can't seem to get it started with record macro as i normally would.

I think i need to set the row and column as dim but i haven't got this far in my learning.

Below is an explanation if someone can help to get me started so i have something to work from (example) {explanation}

Multiple_Object Macro

Selected cell (B3)
Copy Row of selected cell (3)
Insert copied row including formula and formatting to next row where column B is empty (202)
Modify contents of cells:
- If Original (B3) ends in numerical digit add text "a" after existing contents, if already text do nothing
- New (B202) change last character to next in alphabet from (B3) {If B3 ends in "b" B202 ends in "c"}
- Add text "Anomaly with multiple objects, see corresponding reports." before any existing text in original and new column S {S3 and S202}
- Add 1 minute to time in new row column W (W202)
- Clear contents of new row column R & T & U & AI->AS & AW->AY (R202,T202,U202,AI202:AS202,AW202:AY202)
Select column R of new row (R202)
End

Thanks in advance

 

[JLGWhiz]

You would not be able to record a macro of this nature. I think the code below will cover all but the time in column W. That is something you might have to do manually.

Sub t()
Dim rng As Range, newRng As Range, ary As Variant, r As Long, i As Long
Set rng = ActiveCell
    If IsNumeric(Right(rng, 1)) Then
        rng = rng.Value & "a"
    End If
rng.EntireRow.Copy
Cells(Rows.Count, 2).End(xlUp)(2).Offset(, -1).PasteSpecial xlPasteAll
Application.CutCopyMode = False
Set newRng = Cells(Rows.Count, 2).End(xlUp)
newRng = Left(newRng, Len(newRng) - 1) & Chr(Asc(Right(rng.Value, 1)) + 1)
Range("S3") = "Anomaly with multiple objects, see corresponding reports." & Range("S3").Text
newRng.Offset(, 17) = "Anomaly with multiple objects, see corresponding reports." & newRng.Offset(, 17).Text
'Not sure how to handle the time.  don't know format or source.
r = newRng.Row
ary = Array(Range("R" & r), Range("T" & r), Range("U" & r), Range("AI" & r & ":" & "AS" & r), Range("AW" & r & ":" & "AY" & r))
    For i = LBound(ary) To UBound(ary)
        ary(i).ClearContents
    Next
Cells(r, "R").Select
End Sub