VBA Change Event Calulation

Question

I am trying to use the Worksheet Change Event in Excel VBA, but it doesn't seem to working how I thought it would.

I basically want to calculate a cells value (Q2) when the value of another cell (R2) is changed or vise versa.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Range("O:R")) Is Nothing Then Exit Sub

    Application.EnableEvents = False

    If Target.Column = 3 Then
    'User has changed something in column Q:
        Target.Offset(0, 1).Value = Cells(2, 3) * Cells(2, 1)
    If Target.Column = 4 Then
    'User has changed something in column R:
        Target.Offset(0, -1).Value = Cells(2, 3) / Cells(2, 1)
    End If

    Application.EnableEvents = True
End Sub

You need to close off multiline If statements with End IF as well — MacroMarc
– MacroMarc, Commented Mar 19, 2017 at 22:58

MacroMarc · Accepted Answer · 2017-03-19 23:04:08Z

4

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("O:R")) Is Nothing Then Exit Sub

    Application.EnableEvents = False

    'If Target.Column = 17 Then 'CHANGED HERE!
        'User has changed something in column Q:
         'Target.Offset(0, 1).Value = Cells(2, 3) * Cells(2, 1)
    'End If
    'If Target.Column = 18 Then 'CHANGED HERE!
        'User has changed something in column R:
        'Target.Offset(0, -1).Value = Cells(2, 3) / Cells(2, 1)
    'End If

    ' I leave the If-versions above for info, but Select Case is better sometimes
    Select Case Target.Column
    Case 17 ' column Q
        Target.Offset(0, 1).Value = Cells(2, 3) * Cells(2, 1)
    Case 18 ' column R
        Target.Offset(0, -1).Value = Cells(2, 3) / Cells(2, 1)
    End Select

    Application.EnableEvents = True
End Sub

Q Column is number 17, and Column R is number 18 as shown above.

edited Mar 19, 2017 at 23:04

answered Mar 19, 2017 at 22:56

MacroMarc

3,3242 gold badges13 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user4039065 Over a year ago

I'm not sure but I believe that Range("O:R") should be Range("Q:R").

MacroMarc Over a year ago

looked at that Jeeped :) But I though that best left to the OP to fill in if he wants to do something in changing column O

score 3 · Accepted Answer · 2017-03-19 23:57:32Z

3

Don't avoid working with multiple cells as the Target. Intersect can quickly parse down even deleting several full columns to the appropriate range and further restrict to the worksheet's UsedRange.

Add error control, especially to the division operation. A blank cell in A2 will quickly choke the calculation on a 'divide-by-zero'.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    'deal with multiple cells as bel;ow; don't avoid them
    'If Target.Cells.Count > 1 Then Exit Sub

    'use the Intersect to determine if relevant cells have been chanmged
    'note: columns Q:R, not O:R and restrict to the used range
    If Not Intersect(Target, Target.Parent.UsedRange, Range("Q:R")) Is Nothing Then
        On Error GoTo Safe_Exit
        Application.EnableEvents = False
        Dim trgt As Range
        For Each trgt In Intersect(Target, Target.Parent.UsedRange, Range("Q:R"))
            Select Case trgt.Column
                Case 17
                    'guard against multiplying a number by text
                    If Not IsError(Cells(2, 3).Value2 * Cells(2, 1).Value2) Then
                        trgt.Offset(0, 1) = Cells(2, 3).Value2 * Cells(2, 1).Value2
                    End If
                Case 18
                    'guard against possible #DIV/0! error and divding a number by text
                    If Not IsError(Cells(2, 3).Value2 / Cells(2, 1).Value2) Then
                        trgt.Offset(0, -1) = Cells(2, 3).Value2 / Cells(2, 1).Value2
                    End If
            End Select
        Next trgt
    End If  

Safe_Exit:
    Application.EnableEvents = True
End Sub

I'm pretty sure that the actual calculation should involve a variable like trgt.Row but your posted calculation only used C2 and A2 as static cell references to divide/multiply against each other.

edited Mar 19, 2017 at 23:57

answered Mar 19, 2017 at 23:49

user4039065

6 Comments

Spencer Over a year ago

I tried both your code and MacroMarc's code and didn't get any results. Here is my reply to him, because maybe I did not explain what I am trying to do well enough. I probably didn't explain this properly, but I defined O:R because the calculation I am trying to accomplish uses a cell is column O. What I want to calculate is if user changes sell Q2, then R2 = O2 * Q2 or if user changes cell R2, then Q2 = R2 / O2.

user4039065 Over a year ago

I explained that in my narrative; I don't think that small modification is hard to figure out.

Spencer Over a year ago

Well I just started using VBA so sorry if I'm not understanding right away. Is the Cells() function restricted to the defined range, or does it reference the entire worksheet regardless of the defined range?

user4039065 Over a year ago

In a Worksheet_Change (a private sub on a worksheet's code sheet), Cells(row, column) will always refer to that worksheet so column Q is 17 and Q2 is Cells(2, 17) or Cells(2, "Q"). If you see .Cells then look for a With ... End With that the . refers to and use that as a relative reference.

Spencer Over a year ago

Thank you Jeeped. You of course were right about the target.row. I changed Cells(2,17) etc to Cells((Target.Row), 17) and have the results I am looking for.

|

Collectives™ on Stack Overflow

VBA Change Event Calulation

2 Answers 2

2 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related