Microsoft Excel - VBA code to insert line is not working

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

I have the code below. The macro should insert line after name change in column AA but the macro run without inserting lines. 
ActiveCell.Offset(1).EntireRow.Insert

Thanks.



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

On Error Resume Next
LastRow = Range("AA" & Rows.Count).End(xlUp).Row
Range("AD4").Select
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
Else
endRng = ActiveCell.Offset(0, -1).Address
ActiveCell.Offset(1).EntireRow.Insert
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"
    Else
    ActiveCell.Offset(1, 5).Value = newProposeMg
    End If
ActiveCell.Offset(2, 0).Select
currentMg = ActiveCell.Value
startRow = ActiveCell.Offset(0, -1).Address
End If
Loop


   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.

MgCalTool.zip

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.