Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· openaiHugging Face Suzhen/CodeChat

I would like a VBA event for the following. In the Active sheet, the value in D1 can be…

  1. User

    I would like a VBA event for the following.

    In the Active sheet, the value in D1 can be found in range A2:A22 in sheet 'Sector Budget' - this is my SECTOR value.

    In my Active sheet, are text values in the range D6:D305 - these are my SubSector values.

    The SubSector values in D6:D305 can be found in the following rows 2 to 22 in columns C, E, G, I, K, M, O, Q, S, U, W in sheet 'Sector Budget'.

    In the Active sheet, for each entry in D6:D305, I also enter a numeric value Offset(0, 1) in the range E6:E305 - this is my COST.

    In my Active sheet, when I enter a numeric target COST value in the range E6:E305, find and go to the row in A2:A22 of sheet 'Sector Budget' that matches the SECTOR value of D1 in the Active sheet.

    Then on that SECTOR row in sheet 'Sector Budget' find the SubSector value match of the Offset(0, -1) target COST value I entered in the range E6:E305 of the Active sheet'.

    Then, I want the numeric target COST value which I entered in the range E6:E305 of my Active Sheet to be added (not overwritten) to the Offset(0, 1) value in sheet 'Sector Budget' where the SubSector match was found.

  2. Assistant

    Sure, here's a VBA code that can achieve what you described:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim sectorSheet As Worksheet
        Dim activeSheet As Worksheet
        Dim sectorRange As Range
        Dim subSectorRanges As Variant
        Dim subSectorRange As Range
        Dim sectorValue As String
        Dim subSectorValue As String
        Dim costValue As Double
        
        ' Set the worksheets
        Set sectorSheet = ThisWorkbook.Sheets("Sector Budget")
        Set activeSheet = ActiveSheet
        
        ' Set the sector range
        Set sectorRange = sectorSheet.Range("A2:A22")
        
        ' Set the subsector ranges
        subSectorRanges = Array(sectorSheet.Range("C2:C22"), sectorSheet.Range("E2:E22"), sectorSheet.Range("G2:G22"), _
                                sectorSheet.Range("I2:I22"), sectorSheet.Range("K2:K22"), sectorSheet.Range("M2:M22"), _
                                sectorSheet.Range("O2:O22"), sectorSheet.Range("Q2:Q22"), sectorSheet.Range("S2:S22"), _
                                sectorSheet.Range("U2:U22"), sectorSheet.Range("W2:W22"))
        
        ' Check if the changed range is in E6:E305
        If Not Intersect(Target, activeSheet.Range("E6:E305")) Is Nothing Then
            ' Get the sector value from D1
            sectorValue = activeSheet.Range("D1").Value
            
            ' Get the cost value from the adjacent cell
            costValue = Target.Value
            
            ' Loop through the subsector ranges
            For Each subSectorRange In subSectorRanges
                ' Check if the subsector value exists in the range
                If WorksheetFunction.CountIf(subSectorRange, activeSheet.Range("D" & Target.Row).Value) > 0 Then
                    ' Get the subsector value
                    subSectorValue = activeSheet.Range("D" & Target.Row).Value
                    
                    ' Find the matching sector row
                    sectorRow = WorksheetFunction.Match(sectorValue, sectorRange, 0)
                    
                    ' Find the matching subsector column
                    subSectorColumn = WorksheetFunction.Match(subSectorValue, subSectorRange, 0)
                    
                    ' Add the cost value to the corresponding cell in the sector budget sheet
                    sectorSheet.Cells(sectorRow, subSectorColumn).Value = sectorSheet.Cells(sectorRow, subSectorColumn).Value + costValue
                    
                    Exit For
                End If
            Next subSectorRange
        End If
    End Sub
    

    To add this event code to your worksheet:

    1. Press ALT+F11 to open the VBA editor.
    2. Locate your sheet in the Project Explorer window on the left-hand side.
    3. Double-click on the sheet to open the code window.
    4. Paste the code into the code window on the right-hand side.
    5. Close the VBA editor.
    6. Now, whenever you enter a numeric target COST value in the range E6:E305 of the active sheet, the code will find and go to the corresponding row in A2:A22 of the sheet 'Sector Budget'. Then it will find the matching SubSector value in the specified columns and add the target COST value to the existing value in the 'Sector Budget' sheet where the SubSector match was found.
  3. User

    When I run the code, I get a Run-time error '424': Object required, on the line 'If Not Intersect(Target, activeSheet.Range("E6:E305")) Is Nothing Then'

  4. Assistant

    I apologize for the error. The error occurs because the Worksheet_Change event is triggered by any change in the worksheet, including changes in other cells or sheets. To fix this, we need to add a condition to ensure that the event code only runs when the target range is within the specified range. Here's the updated code:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim sectorSheet As Worksheet
        Dim activeSheet As Worksheet
        Dim sectorRange As Range
        Dim subSectorRanges As Variant
        Dim subSectorRange As Range
        Dim sectorValue As String
        Dim subSectorValue As String
        Dim costValue As Double
        
        ' Set the worksheets
        Set sectorSheet = ThisWorkbook.Sheets("Sector Budget")
        Set activeSheet = ActiveSheet
        
        ' Set the sector range
        Set sectorRange = sectorSheet.Range("A2:A22")
        
        ' Set the subsector ranges
        subSectorRanges = Array(sectorSheet.Range("C2:C22"), sectorSheet.Range("E2:E22"), sectorSheet.Range("G2:G22"), _
                                sectorSheet.Range("I2:I22"), sectorSheet.Range("K2:K22"), sectorSheet.Range("M2:M22"), _
                                sectorSheet.Range("O2:O22"), sectorSheet.Range("Q2:Q22"), sectorSheet.Range("S2:S22"), _
                                sectorSheet.Range("U2:U22"), sectorSheet.Range("W2:W22"))
        
        ' Check if the changed range is in E6:E305 and the target range is within the specified range
        If Not Intersect(Target, activeSheet.Range("E6:E305")) Is Nothing And _
            Not Intersect(Target, activeSheet.Range("E6:E305").Resize(, 1)) Is Nothing Then
            ' Get the sector value from D1
            sectorValue = activeSheet.Range("D1").Value
            
            ' Get the cost value from the adjacent cell
            costValue = Target.Value
            
            ' Loop through the subsector ranges
            For Each subSectorRange In subSectorRanges
                ' Check if the subsector value exists in the range
                If WorksheetFunction.CountIf(subSectorRange, activeSheet.Range("D" & Target.Row).Value) > 0 Then
                    ' Get the subsector value
                    subSectorValue = activeSheet.Range("D" & Target.Row).Value
                    
                    ' Find the matching sector row
                    sectorRow = WorksheetFunction.Match(sectorValue, sectorRange, 0)
                    
                    ' Find the matching subsector column
                    subSectorColumn = WorksheetFunction.Match(subSectorValue, subSectorRange, 0)
                    
                    ' Add the cost value to the corresponding cell in the sector budget sheet
                    sectorSheet.Cells(sectorRow, subSectorColumn).Value = sectorSheet.Cells(sectorRow, subSectorColumn).Value + costValue
                    
                    Exit For
                End If
            Next subSectorRange
        End If
    End Sub
    

    Please try running the code again, and it should work without any errors.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.