Excel 2010 VBA Intersect table change accessing current row / column values for updating

[gsaundersbranch]

Maybe I am not providing the best search terms, but I can't find a clear answer (probably due to my lower Excel experience)

Here is an example. Let's say...

• I have a table called tbProjectionsBudget that is populated from a SQL query.
• I have example columns (Item, Proj Qty, Proj Cost, Proj Unit Cost
• If the user changes The Proj Qty I want it to calculate the Proj Cost as Proj Qty * Proj Unit Cost and update the Proj Cost for the row being changed.
• If the user changes the Proj Cost I want it to calculate the Proj Unit Cost as Proj Cost / Proj Qty.
• I am not using formulas as I WANT this to happen behind the scenes, plus the user could alter Proj Qty and Proj Amt which would blow away one or both formulas... plus I just want to know how in VBA with tables not direct cell references like B5 or A2 or whatever.

Below is the code I have and it does work, but want to know if there is a better way. The specific lines I am curious about is the Cells(Target.Row... lines. Is that how I should best do this?

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim KeyCells As Range    Dim ColProjQty As Integer
    Dim ColProjCost As Integer
    Dim ColProjUnitCost As Integer
    
    On Error GoTo ExitME
    
    ' Set Column
    ColProjQty = [tbProjectionsBudget[Proj Qty]].Column
    ColProjCost = [tbProjectionsBudget[Proj Cost]].Column
    ColProjUnitCost = [tbProjectionsBudget[Proj Unit Cost]].Column
        
    ' Get Projected Qty Range
    Set KeyCells = [tbProjectionsBudget[Proj Qty]]
    
    ' If Projected Qty is changed then update Proj Cost based on Proj Qty * Proj Unit Cost
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
        
        Application.EnableEvents = False
        Cells(Target.Row, ColProjCost) = Cells(Target.Row, ColProjQty) * Cells(Target.Row, ColProjUnitCost)
        Application.EnableEvents = True
    
    End If
    
    ' Get Projected Cost Range
    Set KeyCells = [tbProjectionsBudget[Proj Cost]]
    
    ' If Projected Qty is changed then update Projected Cost based on Projected Qty * Proj Unit Cost
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
        Application.EnableEvents = False
        
        If (Cells(Target.Row, ColProjQty) <> 0) Then
            Cells(Target.Row, ColProjUnitCost) = Cells(Target.Row, ColProjCost) / Cells(Target.Row, ColProjQty)
        Else
            Cells(Target.Row, ColProjUnitCost) = 0
        End If
        
        Application.EnableEvents = True
    
    End If


ExitME:
    Application.ScreenUpdating = True
    Application.EnableEvents = True


End Sub

Just having a hard time wrapping my head around accessing specific rows and cells in a table based on where the user is positioned and doing calculations to the row when they make a change.

Thanks,

Greg

 

[gsaundersbranch]

Hello,

I am just following up on my thread as I didn't receive any feedback. Thanks for your time in advance.

Greg