Calculations using vb code?

[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?

 

[Bob Phillips]

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

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

 

[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

 

[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

 

[hoyos]

Thanks Mike,
Does that go in the sheet2 code or the userform code?

 

[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

 

[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?

 

[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

 

[hoyos]

Thanks Mike,
I'v got the idea, it works.
Thanks very much.