Linking equation with a code.

[Shady Salem]

Hi,

I'm operating on a workbook with two sheets, one named "Main" and the other is "Original Data". I would like to link an equation to the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
If Not Intersect(Range("I3:O3"), Target) Is Nothing Then
With Sheets("Main")
.Range("I10") = Target.Value
.Range("O5") = Sheets("Original Data").Range("D3").Value
End With
Sheets("Main").Activate
End If
Application.EnableEvents = True
End Sub

- Equation explanation:
I would like to link this ---> =(G10-K10-O16)/2 <--- to the specified range --> I3:O3 <-- on the code.
So whenever I click from that range; the equation would activate and show the outcome on Q10.

*Note*
Equation cells "G10, K10, O16" and the outcome cell "Q10" are on the "Main" sheet.
I did not fix the equation on the cell directly because I will later on add different ranges than the specified one on the code, and each range will have its own equation.


Please let me know if any details are required.
Thank you.

[SamT]

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
    If Target.Cells.Count > 1 Then Exit Sub 
    Application.EnableEvents = False 
    If Not Intersect(Range("I3:O3"), Target) Is Nothing Then I3O3 Target
    'Add additional Range Sub Calls here

'Sheets("Main").Activate here? or Below?
    Application.EnableEvents = True 
End Sub

Below Codes in a Standard Module

Sub I3O3(Target As Range)
        With Sheets("Main") 
            .Range("I10") = Target.Value 
            .Range("O5") = Sheets("Original Data").Range("D3").Value 
            .Range("Q10") =GetQ10 
        End With 
        Sheets("Main").Activate 
End Sub

Public Function GetQ10() As Double
   With Sheets("Main")
      GetQ10 = (.Range("G10") - .Range("K10") - .Range("O16"))/2
   End With
End Function

[Shady Salem]

Hi, Sam,

Thanks a million. Works exactly as I wanted it to.

[Shady Salem]

I know that you've already mentioned the function in a formula to recalculate by itself, I just don't understand how to do it.
I've set it as a code and I'm facing the auto calculation problem now.

Is there a way for the outcome cell to do the calculation automatically?
Because as I will add different functions with different equations to different ranges, so when I change any of "G10, K10, or O16", I want "Q10" to be able to recalculate.

[SamT]

Hmmmn... I had to edit my previous post to make it right.

Just put the formula =($G$10-$K40-$O$16)/2 in Q10

Then edit this, thusly

Sub I3O3(Target As Range)
        With Sheets("Main")
            .Range("I10") = Target.Value
            .Range("O5") = Sheets("Original Data").Range("D3").Value
        End With
        Sheets("Main").Activate
End Sub

And delete the GetQ10 function.

So you started out wanting Q10 to update when you changed any of I3:O3, now you want it to update when you change any of "G10, K10, or O16"

[Shady Salem]

Sam, I can't fix my formula on the Q10 cell, I've already mentioned that on my description. Becuase Q10 will be the outcome of multiple other formulas assigned with different ranges.

Formula examples:
- (G10-K10-O16)/2
- (G10-K10-O16)/3
- (G10-K10-O16)/4
- (G10-K10-O16)/5
- (G10-K10-O16)/6

Each formula will have its own range, what is in-common between them is that all share the same outcome cell; which is Q10.
My problem is that if I manually change any of the cells in the formula; AKA (G10-K10-or O16) the outcome cell (Q10) does not automatically recalculate to match the change.

I've re-modified:

Public yearCount As Integer




Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    col = Mid(Selection.Address, 2, 1)
    Select Case col
        Case "K"
            yearCount = 2
        Case "L"
            yearCount = 3
        Case "M"
            yearCount = 4
        Case "N"
            yearCount = 5
        Case "O"
            yearCount = 6
    End Select
    
    Application.EnableEvents = False
    If Not Intersect(Range("I3:O3"), Target) Is Nothing Then I3O3 Target
    Application.EnableEvents = True
End Sub


Sub I3O3(Target As Range)
    With Sheets("Main")
        .Range("I10") = Target.Value
        .Range("O5") = Sheets("Original Data").Range("D3").Value
        .Range("Q10") = GetQ10
    End With
    Sheets("Main").Activate
End Sub


Public Function GetQ10() As Double
    With Sheets("Main")
        GetQ10 = (.Range("G10") - .Range("K10") - .Range("O16")) / yearCount
        
    End With
End Function

[SamT]

To update Q10 when the sheet Calculates:
Put this in the "Main" Code page

Private Sub Worksheet_Calculate()
   UpDate_Q10
End Sub

Sub Update_Q10()
  Sheets("Main").Range("Q10") = GetQ10
End Sub

[SamT]

You are overloading the Selection Change Event sub. Edit it thusly:

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
    If Target.Cells.Count > 1 Then Exit Sub 

   If not Intersect(Target, Range("K:O")) Is Nothing Then SetYearCount 'Refine address to include Rows
     
'IMO, it's best to Set the application properties in the subordinate subs and not in the Event Subs    
Application.EnableEvents = False 
    If Not Intersect(Range("I3:O3"), Target) Is Nothing Then I3O3 Target 
    Application.EnableEvents = True 
End Sub 

Sub SetYearCount(Target As Range)
   YearCount = Target.Column - 9
End Sub

Note that in the case of one line subs like this, it is fine to replace the Sub Call with the one line of code

 If not Intersect(Target, Range("K:O")) Is Nothing Then YearCount = Target.Column - 9 'Refine address to include Rows

When the wokbook opens, All Variables are uninitialized, IWO, YearCount is zero. That can Raise Errors. Add these lines to the start of GetQ10

IF YearCount < 2 Then YearCount = 2
MsgBox "YEarCount is not initialized"

This will prevent Excel from erroring out, but will give a visual indicator to you.

[Shady Salem]

Hi, Sam,

The update code doesn't show the sum on the box if I change the values, it clears it.
Any ideas?

[SamT]

Upload the newest vesrion of the book.

[Shady Salem]

Uploaded!
Now O14 with Main has to update as well whenever the values are changed.

Sorry for not using the second code when you said that I'm overloading the event, I'm a newbie with the codes.
Plus I keep adding modifications.

[SamT]

Sorry for not using the second code when you said that I'm overloading the event,

I noticed that YearCount was = Target.Column - 9. If you want to use the Select Case, just move it to the SetYearCount function.

A large part of the problem is that you are using Merged Cells. That forces you to use code structures like

Sub I3O3(Target As Range)
    With Sheets("Main")
        .Range("I10") = Target.Value
        .Range("O5") = Sheets("Original Data").Range("D3").Value
        .Range("G5") = Sheets("Original Data").Range("A2").Value
        .Range("I5") = Sheets("Original Data").Range("C3").Value
        .Range("K5") = Sheets("Original Data").Range("B3").Value
        .Range("O14") = GetO14
        .Range("Q10") = GetQ10
    End With
    Sheets("Main").Activate
End Sub

Sub I4O4(Target As Range)
    With Sheets("Main")
        .Range("I10") = Target.Value
        .Range("O5") = Sheets("Original Data").Range("D4").Value
        .Range("Q10") = GetQ10
    End With
    Sheets("Main").Activate
End Sub

Using that structure means that you MUST remember to select the appropriate row to fill in parts of Main before you then select the Row to fill in the rest of Main.

Range("A2:A13") is merged. IF you unmerge the range and put "Building 1" in all the cells, and did the same for all the Buildings...

This Code would replace all of your current code

'Standard Module Code
Public YearCount As Long

'Main Code
Private Sub Worksheet_Change(ByVal Target As Range)
 Range("Q10") = (Range("G10") - Range("K10") - Range("O14")) / YearCount
End Sub

'Original Data Code
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim PayPlan As Worksheet
Dim OData As Worksheet
Dim Rw As Long

Set PayPlan = Sheets("Main")
Set OData = Sheets("Original Data")
Rw = Target.Row
YearCount = Target.Column - 9

    With PayPlan
        .Range("G5") = OData.Cells(Rw, "A")
        .Range("I5") = OData.Cells(Rw, "C")
        .Range("K5") = OData.Cells(Rw, "B")
        .Range("O5") = OData.Cells(Rw, "D")
        .Range("I10") = Target.Value
        '.Range("O10") = ???
        .Range("O14") = .Range("O10") * YearCount * 12
        .Range("Q10") = (.Range("G10") - .Range("K10") - .Range("O14")) / YearCount
    End With
    Sheets("Main").Activate
End Sub

[Shady Salem]

Can I ask you to apply the changes on the sheet and re-attach it?
I'm getting all sorts of errors when I test it.

[SamT]

Original Data sheet.xlsm

[Shady Salem]

The book that you've uploaded is missing the Main sheet and it has no codes.

[SamT]

It shows the changes applied to the sheet. That is what you asked for.

It seems that you are not real sure what you want. Maybe I am misunderstanding. Try to be more clear.

You also need to unmerge all the data cells on Main. VBA has a hard time with Merged Cells.

[Shady Salem]

Sam, I'm asking you to apply the latest code modifications that you've recommended on the book and attach it here after testing it, because I got multiple errors and some functions weren't even responding.

[SamT]

All I did was

• Delete all other code
• Unmerge the data cells on Main
• Change the I10 assignment from Target to Cells(rw, "I")
• Restrict the running of the code to the Years Range.

You must still edit Original Data to be the same as the last attachment to fix the Building Number issue.

[Shady Salem]

I would like to thank you very much for the assistance.
I know that I've been a major pain, still learning.

I still have the O14 and Q10 cells issue, not automatically updating when I change the values on other fields.
If there's nothing that can be done about it, I guess I'll just have to re-select the fields again from the Original Data sheet.

[SamT]

I still have the O14 and Q10 cells issue, not automatically updating when I change the values on other fields.
If there's nothing that can be done about it, I guess I'll just have to re-select the fields again from the Original Data sheet.

I don't know why that is, but I am not real clear on which sheet all those ranges and cells you mention are actually on, You never specify.

When I tested it with my best guesses, everything that could work did work. Some things could not work because all the building numbers were not present, because I got tired of dragging each hidden Row open one by one. I did that once, and once was enough.

I, personally, would have worked this project from the other direction: First, I would have used a VBA UserForm instead of a Worksheet Form.

With a Worksheet Form, I would want to Click on the Building Cell in Main and have a list of values pop up to select from. This selection would set the list of values in the next Cell to be selected from, and so forth until all selections had been made

I would not want the Client ever seeing the Original Data Sheet. All I want them to see is the beautiful Form I create. I want to keep all other details hidden from the Client.