I would like a VBA event for the following. In the Active sheet, the value in D1 can be…
- 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.
- 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 SubTo add this event code to your worksheet:
- Press ALT+F11 to open the VBA editor.
- Locate your sheet in the Project Explorer window on the left-hand side.
- Double-click on the sheet to open the code window.
- Paste the code into the code window on the right-hand side.
- Close the VBA editor.
- 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.
- 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'
- Assistant
I apologize for the error. The error occurs because the
Worksheet_Changeevent 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 SubPlease try running the code again, and it should work without any errors.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.