Small Macro running slow

[Stephenosn]

Hello all,

I'm having trouble with a small micro running much slower than I believe it should. Please see the code bellow.

Sub NBill_Click()

Dim NBill As Integer
    NBill = MsgBox("This action will update the previous percent complete and the total billed to date?", _
    vbOKCancel, "NEW BILLING PERIOD?")
    If NBill = vbCancel Then
        Exit Sub
    End If
     
    'Add Amount to bill this period to total amount billed to-date
    Dim ABill As Object
          For Each ABill In Range("J5:J60")
              ABill.Value = ABill.Offset(0, 0).Value _
                  + ABill.Offset(0, -1).Value
          Next
'Replace previous percent complete with percent completed to-Date
    Dim rng As Range
        Set rng = Worksheets("SOV").Range("H5:H60")
            Worksheets("SOV").Range("F5").Resize _
            (rng.Rows.Count, rng.Columns.Count).Cells.Value = rng.Cells.Value
End Sub

I have walked through the code and it appears the problem is with the following procedure.

'Add Amount to bill this period to total amount billed to-date    Dim ABill As Object
          For Each ABill In Range("J5:J60")
              ABill.Value = ABill.Offset(0, 0).Value _
                  + ABill.Offset(0, -1).Value
          Next

Please let me know if there is something I'm missing here that would speed this macro up or a better way to write the code.

Thank you for having a look,

John

 

[Stephenosn]

Rhodie,

Thank you for the thorough examination of my code. I'm going to give this a try and I'll let you know how it goes.

Thanks again,

John

 

[JoeMo]

No, I don't imagine it does. I think the problem is that you are referencing data directly on the sheet instead of lifting it into memory first and operating on the array of data in memory instead. you have a set array in

I agree doing it all in memory will be faster, but the number of cells involved is so small (tens of cells) that it's hard to believe that it will make a perceptible difference on the scale of human reaction time.

 

[Rhodie72]

I agree doing it all in memory will be faster, but the number of cells involved is so small (tens of cells) that it's hard to believe that it will make a perceptible difference on the scale of human reaction time.

It depends on the data sets in the worksheets and the formulas that are embedded in them too. We can't see what else has been written into the rest of the sheets.

 

[Stephenosn]

Sorry for my delay in getting back to you.

Rhodie72, I've placed you're code behind the button and I've still got the lag. The only formulas on this sheet are as follows.

=IF(SOV!$H5<>"",SOV!$H5/100-SOV!$F5/100,"")

=IFERROR((SOV!$C5*SOV!$G5),"")

and

=IF(C5<>"",C5*0.1,"")

This may be a dumb question, but when this is run does it somehow run other code I have in my workbook? There doesn't seem to be lag with anything else. I can provide a link to my workbook with sample data if that would help.

Thank you all for the great help so far.

John

 

[JoeMo]

Sorry for my delay in getting back to you.

Rhodie72, I've placed you're code behind the button and I've still got the lag. The only formulas on this sheet are as follows.

=IF(SOV!$H5<>"",SOV!$H5/100-SOV!$F5/100,"")

=IFERROR((SOV!$C5*SOV!$G5),"")

and

=IF(C5<>"",C5*0.1,"")

This may be a dumb question, but when this is run does it somehow run other code I have in my workbook? There doesn't seem to be lag with anything else. I can provide a link to my workbook with sample data if that would help.

Thank you all for the great help so far.

John

As I pointed out in post #12, Rhodie's solution will make no perceptible difference in this case. It's possible that other code in the workbook is causing delay which is why I asked about other code in post #8.