Formula in VBA rather than in cell

ChristineJ

Well-known Member
Joined
May 18, 2009
Messages
756
Office Version
  1. 365
Platform
  1. Windows
I'd like to see how this can be done in VBA, without actually using any formulas in the worksheet.

IF the value in P10 = D9*E9, the word "OK" should appear in Q10.

This "OK" in Q10 would be triggered as soon as the entry is made in P10.

Essentially it is the same as having =IF(D9*E9=P10,"OK","") in Q10, except I want the formula in VBA rather than in cell Q10.

Thanks, CJ
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a value in column P and press the RETURN a key. This macro will be triggered for any cell in column P. If you want it triggered for just P10, please let me know.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("P:P")) Is Nothing Then Exit Sub
    If Target.Value = Target.Offset(-1, -12).Value * Target.Offset(-1, -11).Value Then
        Target.Offset(, 1) = "OK"
    End If
End Sub
 
Upvote 0
This is excellent. I do want it triggered for just P10, so I changed to If Intersect(Target, Range("P10:P10") Seems to work - is that the correct way to handle it?

I also want to trigger P11, P12, and several more, but the formula will be different for each - not just multiplying two other cell values. I assume to do that I would just have to repeat what you have between the first and last lines of your code, change P10:P10 to P11:11, etc. and change the desired formula. Will that work?

Can you tell me what If Target.CountLarge > 1 does?

I am really a novice at VBA. Thanks so much for the quick response. C
 
Upvote 0
Please list all the cells with their corresponding formulae.
Can you tell me what If Target.CountLarge > 1 does?
This restricts you to entering data in only one cell at a time in column P.
 
Upvote 0
Thanks for the info. Here are the formulas for several cells in column P:

P10: =D9*E9
P11: =D9+H22-H15
P12: =(P11*D27+D22-D15)*D32+(P11*D28+D23-D16)*D33
P13: =SUMPRODUCT(E37:E40*F37:F40)*P11
P14: =SUM(F44:F48)

I do not necessarily have to use functions but instead can just add and/or multiply individual cells.

Any possibility of using your code above to deal with different formulas in each cell?

Thanks! C
 
Upvote 0
Try:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("P10:P14")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Dim rng As Range, total As Long, prod As Long
    Select Case Target.Row
        Case Is = 10
            If Target.Value = Range("D9") * Range("E9") Then
                Target.Offset(, 1) = "OK"
            End If
        Case Is = 11
            If Target.Value = Range("D9") + Range("H22") - Range("H15") Then
                Target.Offset(, 1) = "OK"
            End If
        Case Is = 12
            If Target.Value = (Range("P11") * Range("D27") + Range("D22") - Range("D15")) * Range("D32") + (Range("P11") * Range("D28") + Range("D23") - Range("D16")) * Range("D33") Then
                Target.Offset(, 1) = "OK"
            End If
        Case Is = 13
            For Each rng In Range("E37:E40")
                total = total + rng * rng.Offset(, 1)
            Next rng
            prod = total * Range("P11")
            If Target.Value = prod Then
                Target.Offset(, 1) = "OK"
            End If
        Case Is = 14
            If Target.Value = WorksheetFunction.Sum(Range("F44:F48")) Then
                Target.Offset(, 1) = "OK"
            End If
    End Select
    Application.EnableEvents = True
End Sub
 
Upvote 0
Solution
Very awesome! I works perfectly and it also makes sense to me so that I can use it for future calculations.

Thank you so much for providing this code and for sticking with me on it! CJ
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top