Add formulae to rows that have data

[dwg83]

This code is running extremely slow. Is there a better way to do this so it won't be agonizingly slow.

Thanks!

    'adds formulae to rows that have data
    If Target.Column = 1 Then
        For Each i In Target.Rows
            If Cells(i.Row, 1) <> "" Then
                Cells(i.Row, 16) = "=IF(ISERROR(Q" & i.Row & "/R" & i.Row & "),"""",Q" & i.Row & "/R" & i.Row & ")"
                Cells(i.Row, 17) = "=IF(AND((B" & i.Row & "-C" & i.Row & ")>0,R" & i.Row & ">3),B" & i.Row & "-C" & i.Row & ",0)"
                Cells(i.Row, 18) = "=IF((B" & i.Row & "-M" & i.Row & ")>3,B" & i.Row & "-M" & i.Row & ",0)"
            Else
                Cells(i.Row, 16) = ""
                Cells(i.Row, 17) = ""
                Cells(i.Row, 18) = ""
            End If
        Next
    End If

 

[Joe4]

Is this part of a Worksheet_Change event procedure?
If so, you probably want to disable the events before making the updates so that the procedure doesn't keep calling itself as you change values.

Application.EnableEvents=False

Then when you are finished, turn events back on with

Application.EnableEvents=True

 

[dwg83]

Joe 4,

Thanks for that response, we were able to cut the time in half. i.e. For 8000 rows, we were able to reduce the time from 10 seconds to 5 seconds. If anyone else has recommendations on how to reduce the time it takes to fill in these formulae, please let me know!

Thanks again!

David

 

[Joe4]

Can you post your entire code, and explain how the data is being entered (what exactly is happening to trigger this code)?

 

[dwg83]

After stepping through the code for the worksheet change event, the only thing causing the long processing time is the code I provided. Your addition of the Enable events did its job to prevent going back through any of the worksheet change code again. I am wondering if I put the formulae in the first row and then filled in the rest of the rows as you would dragging down in excel would be faster. That is what I am trying next. Unfortunately, that will not be able to handle blanks in the middle of the range, it will just fill in those rows too.

Thanks!
David

 

[Joe4]

Another thing you can try is to put
Application.ScreenUpdating = False
at the top of your code and set it back at the end like this:
Application.ScreenUpdating = True