# Dispaly and use of variables

#### NiteOwls

##### New Member
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

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### Gary McMaster

##### Well-known Member
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

Code:
``````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

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

¬JH

Replies
3
Views
559
Replies
3
Views
330
Replies
2
Views
175
Replies
12
Views
357
Replies
2
Views
241

1,191,171
Messages
5,985,067
Members
439,938
Latest member
MAlhash

### 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.

### Which adblocker are you using?

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

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