Microsoft Excel - VBA code to insert line is not working

Asked By Cherifa Hima on 29-Jan-13 07:10 PM

I have the code below. The macro should insert line after name change in column AA but the macro run without inserting lines. 


Sub Comp()
Application.ScreenUpdating = False
Dim lrow As Integer

On Error Resume Next
LastRow = Range("AA" & Rows.Count).End(xlUp).Row
startRow = ActiveCell.Offset(0, -1).Address
currentMg = ActiveCell.Value

Do Until ActiveCell.Value = ""

AvgMg = Nothing
MedMG = Nothing
ModMG = Nothing

If ActiveCell.Offset(1, -3).Value = ActiveCell.Offset(0, -3).Value Then
ActiveCell.Offset(1, 0).Select
endRng = ActiveCell.Offset(0, -1).Address
AvgMg = WorksheetFunction.Average(Range(startRow & ":" & endRng))
MedMG = WorksheetFunction.Median(Range(startRow & ":" & endRng))
ModMG = WorksheetFunction.Mode(Range(startRow & ":" & endRng))
ActiveCell.Offset(1, 1).Value = AvgMg
ActiveCell.Offset(1, 2).Value = MedMG
ActiveCell.Offset(1, 3).Value = ModMG
newProposeMg = (AvgMg + MedMG + ModMG) / WorksheetFunction.Count(Range("AE" & ActiveCell.Offset(1, 0).Row & ":AG" & ActiveCell.Offset(1, 0).Row))
    If currentMg + (currentMg * 0.03) >= newProposeMg And currentMg - (currentMg * 0.03) <= newProposeMg Then
    ActiveCell.Offset(1, 4).Value = "Keep"
    ActiveCell.Offset(1, 5).Value = newProposeMg
    End If
ActiveCell.Offset(2, 0).Select
currentMg = ActiveCell.Value
startRow = ActiveCell.Offset(0, -1).Address
End If

   Application.ScreenUpdating = True
   MsgBox "Done."
   End Sub

Pichart Y. replied to Cherifa Hima on 30-Jan-13 03:51 AM
Hi Cherifa Hima,

It works fine. There is no problem here. I do nothing with your code.

So, I think this may be, you have some column protection or you have something in in the last row of your spreadsheet, then excel cannot shift the row down anymore...error alert message, then.

pichart Y.
Cherifa Hima replied to Pichart Y. on 30-Jan-13 09:51 AM
Oh I had two columns in the database without column titles. I added the titles and it worked fine.
Thanks as always. I really appreciate your help.
Pichart Y. replied to Cherifa Hima on 31-Jan-13 02:51 AM
you'r welcome!! then

see you

pichart Y.