VBA - multiply columns

Question

I am running a small VBA to loop through a range of rows (27 - 52) to return a simple multiplication of column D X column E to column F.

My code below crashes Excel. Can anyone point out the obvious as to where i am going wrong. I am clearly no expert!

Private Sub Worksheet_Change(ByVal Target As Range)

For i = 27 To 52

  Cells(i, 6) = Cells(i, 4) * Cells(i, 5)

Next i

End Sub

Thanks

never ending loop....because you are running it in worksheet change. How do you want the code to activate? From an button? or a certain cell? — ThisGuyJustNeedsHelp, Dec 05 '18 at 21:52
You may find [this](https://stackoverflow.com/questions/15761530/vba-worksheet-change-event-bypass) to be useful — cybernetic.nomad, Dec 05 '18 at 21:56

score 4 · Answer 1 · answered Dec 05 '18 at 22:03

4

Excel will crash as your code is creating an infinite loop, due to Change Event that you are using. Every time your code multiplies two given cells it is being identified as a change event, so your code will be triggered again, and again...

You can either use the adjusted code that Michal has posted, or you can adjust your original code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    For i = 27 To 52
        Cells(i, 6) = Cells(i, 4) * Cells(i, 5)
    Next i
    Application.EnableEvents = True
End Sub

Hope it helps!

answered Dec 05 '18 at 22:03

Justyna MK

3,523
3
11
25

Michael and Justyna...thank you for your assistance on the above. I just got around to fixing this and am using the Worksheet_Change event. If i may ask, in the request above i was simply trying to automate a multiplication of column D X column E to column F. Would there be a way for the user to be able to 'override' the multiplication in column F to provide them with two options? – Vietman78 Apr 18 '19 at 02:15

score -1 · Accepted Answer · answered Dec 05 '18 at 21:53

-1

I don't see how it would "crash" Excel, abyway you don't need Change(ByVal Target As Range)so it's simply:

Private Sub foo()

    For i = 27 To 52

      Cells(i, 6).Value = Cells(i, 4).Value * Cells(i, 5).Value

    Next i

End Sub

answered Dec 05 '18 at 21:53

Michal Rosa

2,459
2
15
28

I believe the issue is the code itself triggers a worksheet change. ...which triggers the macro, which triggers a worksheet change, which triggers the macro, which ... – BruceWayne Dec 05 '18 at 22:25

VBA - multiply columns

2 Answers2