PDA

View Full Version : Linking equation with a code.

03-16-2016, 05:13 AM
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
03-16-2016, 07:42 PM
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

'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

03-17-2016, 01:59 AM
Hi, Sam,

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

03-17-2016, 02:30 AM
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
03-17-2016, 01:42 PM
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"

03-17-2016, 03:33 PM
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
03-17-2016, 07:55 PM
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
03-17-2016, 08:15 PM
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.

03-18-2016, 07:29 AM
Hi, Sam,

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

SamT
03-18-2016, 07:47 AM
Upload the newest vesrion of the book.

03-18-2016, 08:32 AM
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
03-18-2016, 04:14 PM
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

03-19-2016, 03:34 AM
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
03-19-2016, 10:30 AM
15700

03-19-2016, 11:47 AM
The book that you've uploaded is missing the Main sheet and it has no codes.

SamT
03-19-2016, 04:46 PM
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.

03-19-2016, 11:57 PM
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
03-20-2016, 08:47 AM
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.

03-20-2016, 09:07 AM
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
03-20-2016, 01:45 PM
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.