Calculations using vb code?

H

hoyos

How can I the following formula into a vb code.

=SUM(Sheet2!B1/28)+Sheet2!B3
and
+SUM(1013-Sheet2!B4)*28

Any ideas?
 
B

Bob Phillips

Range("A1").Formula = "=SUM(Sheet2!B1/28)+Sheet2!B3"

Range("B1").Formula = "=SUM(1013-Sheet2!B4)*28"
 
M

Mike H

Hi,

Sub addup()
Dim MSht As Variant
Set MSht = Sheets("Sheet2")
With MSht
MTot = .Range("B1") / 28 + .Range("B3")
MyOthertot = (1013 - .Range("B4")) * 28
End With
End Sub


Mike
 
B

Bernie Deitrick

Dim myV1 As Double
myV1 = Worksheets("Sheet2").Range("B1").Value/28 +
Worksheets("Sheet2").Range("B3").Value

Dim myV2 As Double

myV2 = (1013 - Worksheets("Sheet2").Range("B4").Value)*28

HTH,
Bernie
 
M

Mike H

Hi,

Because I named the sheet in the WITH statement it can go in either
including if you want a general module.

If your using it in conjunction with a userform then you need a method of
calling the code which could be a button on the userform or an event
associated with the userform.

Mike
 
H

hoyos

Thanks Mike,
Thats understood. But if I wanted to use a spinbutton(4) which controls the
value of Textbox17 which in turn is linked to Sheet2 cell "B3". Can I insert
this code in spinbutton(4) code?
 
M

Mike H

Hi,

I hope this demonstrates the basic idea. As spinbutton changes the value is
put into textbox17.

The code I gave you goes in the change event for text box 17 and executes
brcause the value is changing and up dates textbooxes 18 & 19 with the
results of the sum.

Private Sub SpinButton4_Change()
TextBox17.Value = SpinButton4.Value
End Sub

Private Sub TextBox17_Change()
Dim MSht As Variant
Set MSht = Sheets("Sheet2")
With MSht
TextBox18 = .Range("B1") / 28 + .Range("B3")
TextBox19 = (1013 - .Range("B4")) * 28
End With
End Sub

Mike
 
D

David Biddulph

Perhaps you could explain to us what you are expecting the SUM function to
do for you if you give it only one argument?
In what way do you expect =SUM(Sheet2!B1/28) to differ from =Sheet2!B1/28 ?
In what way do you expect SUM(1013-Sheet2!B4)*28 to differ from
(1013-Sheet2!B4)*28 ?
If you don't understand what the SUM function does, you'll find the details
in Excel help.
 
Top