Dispaly and use of variables

[NiteOwls]

Hi all,

New to excel programming I'm strugling with the following:

I have 2 buttons on my excel sheet called Male and Female. They call the following macro's:

Sub Male()
Dim Gender As String, GrandTotal As Integer
If Rows("96:97").EntireRow.Hidden = False Then
Rows("96:97").EntireRow.Hidden = True
Gender = "Male"
GrandTotal = 87
MsgBox "the value of Gender is " & Gender & _
Chr(13) & "the value of Grand Total is " & GrandTotal
End If
End Sub

Sub Female()
Dim Gender As String, GrandTotal As Integer
If Rows("96:97").EntireRow.Hidden = True Then
Rows("96:97").EntireRow.Hidden = False
Gender = "Female"
GrandTotal = 89
MsgBox "the value of Gender is " & Gender & _
Chr(13) & "the value of Grand Total is " & GrandTotal
End If
End Sub

When I hit either button I can see the variables Gender and GrandTotal being set correctly.

Now I need to use value of GrandTotal in the sheet to calculate outcomes of questions to give them points and to substract then the points of the questions from the grand total.

I fail to get the value of GrandTotal in my calculation, I tried several things like this:
=GrandTotal-SUM(B145,B147,B149,B153,B155,B157)
=[& GrandTotal]-SUM(B145,B147,B149,B153,B155,B157)
=(& GrandTotal)-SUM(B145,B147,B149,B153,B155,B157)

How do I get the value of GrandTotal (87 or 89) into the calculation?

Thanks
¬JH

 

[Gary McMaster]

Here are a couple of ways to do it. Of course you could also do the addition right in the macro and just put the results in the correct cell.

Hope it helps.

Gary

Dim GrandTotal As Double

GrandTotal = 87

'Put the value of "GrandTotal" directly into the formula
ActiveSheet.Range("$A$1").Formula = "=" & GrandTotal & "-SUM(B145,B147,B149,B153,B155,B157)"

'Put the value of "GrandTotal" in cell A2 and reference A2 in the formula
ActiveSheet.Range("$A$2").Value = GrandTotal
ActiveSheet.Range("$B$2").Formula = "=A2-SUM(B145,B147,B149,B153,B155,B157)"

 

[NiteOwls]

Many thanks for your help, this is what I was looking for

¬JH