| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculations using vb code?
How can I the following formula into a vb code.
=SUM(Sheet2!B1/28)+Sheet2!B3 and +SUM(1013-Sheet2!B4)*28 Any ideas? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculations using vb code?
Range("A1").Formula = "=SUM(Sheet2!B1/28)+Sheet2!B3"
Range("B1").Formula = "=SUM(1013-Sheet2!B4)*28" -- __________________________________ HTH Bob "hoyos" wrote in message ... How can I the following formula into a vb code. =SUM(Sheet2!B1/28)+Sheet2!B3 and +SUM(1013-Sheet2!B4)*28 Any ideas? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculations using vb code?
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 "hoyos" wrote: How can I the following formula into a vb code. =SUM(Sheet2!B1/28)+Sheet2!B3 and +SUM(1013-Sheet2!B4)*28 Any ideas? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculations using vb code?
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" wrote in message ... How can I the following formula into a vb code. =SUM(Sheet2!B1/28)+Sheet2!B3 and +SUM(1013-Sheet2!B4)*28 Any ideas? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculations using vb code?
Thanks Mike,
Does that go in the sheet2 code or the userform code? "Mike H" wrote: 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 "hoyos" wrote: How can I the following formula into a vb code. =SUM(Sheet2!B1/28)+Sheet2!B3 and +SUM(1013-Sheet2!B4)*28 Any ideas? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculations using vb code?
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" wrote: Thanks Mike, Does that go in the sheet2 code or the userform code? "Mike H" wrote: 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 "hoyos" wrote: How can I the following formula into a vb code. =SUM(Sheet2!B1/28)+Sheet2!B3 and +SUM(1013-Sheet2!B4)*28 Any ideas? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculations using vb code?
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" wrote: 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" wrote: Thanks Mike, Does that go in the sheet2 code or the userform code? "Mike H" wrote: 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 "hoyos" wrote: How can I the following formula into a vb code. =SUM(Sheet2!B1/28)+Sheet2!B3 and +SUM(1013-Sheet2!B4)*28 Any ideas? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculations using vb code?
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" wrote: 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" wrote: 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" wrote: Thanks Mike, Does that go in the sheet2 code or the userform code? "Mike H" wrote: 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 "hoyos" wrote: How can I the following formula into a vb code. =SUM(Sheet2!B1/28)+Sheet2!B3 and +SUM(1013-Sheet2!B4)*28 Any ideas? |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculations using vb code?
Thanks Mike,
I'v got the idea, it works. Thanks very much. "Mike H" wrote: 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" wrote: 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" wrote: 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" wrote: Thanks Mike, Does that go in the sheet2 code or the userform code? "Mike H" wrote: 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 "hoyos" wrote: How can I the following formula into a vb code. =SUM(Sheet2!B1/28)+Sheet2!B3 and +SUM(1013-Sheet2!B4)*28 Any ideas? |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculations using vb code?
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. -- David Biddulph hoyos wrote: How can I the following formula into a vb code. =SUM(Sheet2!B1/28)+Sheet2!B3 and +SUM(1013-Sheet2!B4)*28 Any ideas? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
| Code to conditional format all black after date specified in code? | Excel Discussion (Misc queries) | |||
| Drop Down/List w/Code and Definition, only code entered when selec | Excel Worksheet Functions | |||
| Help! creating code for time to decimal calculations | Excel Worksheet Functions | |||
| copying vba code to a standard code module | Excel Discussion (Misc queries) | |||
Linear Mode
