VBA to validate calculated cell, if value > 0 add value of another cell

[jmd78]

I am using a SUMIF statement to get a value for cell "I21." What I want to do is use VBA to validate that the calculated cell value for I21 is greater than 0 and if YES "add" the value of cell G21, if NO do nothing and move on to I22 etc.

Cell I21 has the formula =SUMIF('HME100105_Draw 1_Wrksht'!$A$7:$A$35,$B21,'HME100105_Draw 1_Wrksht'!$F$7:$F$35). I want to validate the calculated cell value is > 0 and if "YES" then add to that calculated value the value of cell G21. The I21 calculated value = 447.93, therefore the cell value is >0, so add the cell value of G21 = -50.00 to realize a new I21 cell value of 397.93.

 

[aaewalsh]

To accomplish this task, you will need to use VBA to write a macro that performs the validation and calculation.

Sub ValidateAndCalculate()
    'Retrieve the calculated value of cell I21
    I21Value = Range("I21").Value
    'Check if the value is greater than 0
    If I21Value > 0 Then
        'If the value is greater than 0, add the value of cell G21 to the value of cell I21
        I21Value = I21Value + Range("G21").Value
        'Update the value of cell I21 with the new value
        Range("I21").Value = I21Value
    End If
End Sub

You would then need to run this macro to perform the validation and calculation.

 

[jmd78]

To accomplish this task, you will need to use VBA to write a macro that performs the validation and calculation.

Sub ValidateAndCalculate()
    'Retrieve the calculated value of cell I21
    I21Value = Range("I21").Value
    'Check if the value is greater than 0
    If I21Value > 0 Then
        'If the value is greater than 0, add the value of cell G21 to the value of cell I21
        I21Value = I21Value + Range("G21").Value
        'Update the value of cell I21 with the new value
        Range("I21").Value = I21Value
    End If
End Sub

You would then need to run this macro to perform the validation and calculation.

Works brilliantly, I very much appreciate the explanation for each line of code. How does one apply this to the worksheet cell range I21 & g21 respectively through I95 & G95

 

[aaewalsh]

You could try:

Sub ValidateAndCalculate()
    'Define the range of cells to check
    Set I_Range = Range("I21:I95")
    Set G_Range = Range("G21:G95")
    'Loop through each cell in the range
    For Each I_Cell In I_Range
        'Check if the value is greater than 0
        If I_Cell.Value > 0 Then
            'If the value is greater than 0, add the value of corresponding G cell to the value of I cell
            I_Cell.Value = I_Cell.Value + G_Range(I_Cell.Row - I_Range.Row + 1, 1).Value
        End If
    Next I_Cell
End Sub