Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA Macro needed
Hi,
I start with the initial Ownership of Pool 1 which owns a portion of Pool 2. I need a VBA Macro that would return the final ownership based on the inital ownership. Ownership must equal 1. Initial Ownership Final Ownership Pool 1 Owner A 0.25 Pool 1 Owner A 0.375 Pool 1 Owner B 0.25 Pool 1 Owner B 0.375 Pool 1 Owner C 0.25 Pool 1 Owner C 0.25 Pool 1 Pool 2 0.25 Pool 2 Owner A 0.5 Pool 2 Owner B 0.5 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA Macro needed
Hi Jeff
I think you need to write a formula instead of VBA macro. Final Ownership, Pool1 Owner A = His share from pool 1 + Share of pool 2 which is 0.25 multiply by his share from Pool 2. ex. Final Ownership Pool 1 Owner A = 0.25 + 0.25*0.5 = 0.375 I hope this will help. "Jeff" wrote: Hi, I start with the initial Ownership of Pool 1 which owns a portion of Pool 2. I need a VBA Macro that would return the final ownership based on the inital ownership. Ownership must equal 1. Initial Ownership Final Ownership Pool 1 Owner A 0.25 Pool 1 Owner A 0.375 Pool 1 Owner B 0.25 Pool 1 Owner B 0.375 Pool 1 Owner C 0.25 Pool 1 Owner C 0.25 Pool 1 Pool 2 0.25 Pool 2 Owner A 0.5 Pool 2 Owner B 0.5 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA Macro needed
Thank you.
What I had in mind was a inputbox to input Pool 1 then the returned values should equal Final Ownership. "Yara" wrote: Hi Jeff I think you need to write a formula instead of VBA macro. Final Ownership, Pool1 Owner A = His share from pool 1 + Share of pool 2 which is 0.25 multiply by his share from Pool 2. ex. Final Ownership Pool 1 Owner A = 0.25 + 0.25*0.5 = 0.375 I hope this will help. "Jeff" wrote: Hi, I start with the initial Ownership of Pool 1 which owns a portion of Pool 2. I need a VBA Macro that would return the final ownership based on the inital ownership. Ownership must equal 1. Initial Ownership Final Ownership Pool 1 Owner A 0.25 Pool 1 Owner A 0.375 Pool 1 Owner B 0.25 Pool 1 Owner B 0.375 Pool 1 Owner C 0.25 Pool 1 Owner C 0.25 Pool 1 Pool 2 0.25 Pool 2 Owner A 0.5 Pool 2 Owner B 0.5 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA Macro needed
Hi Jeff
I hope this will give you a good idea: First you have to show Developer tab on the ribbon how, press Office button --- select Excel options from Popular check the box show Developer tab on the ribbon Then select the Developer tab --- select Design Mode from Controls tab Then select Insert also from Controls tab --- select Command Button from ActiveX group Draw the button anywhere --- right click on the button --- select view code VBA will be opened, write down the following starting down under Sub Private .... Dim pool1OwnerA, pool1OwnerB, pool1OwnerC, pool1OwnerPool2 As Single Dim pool2OwnerA, pool2OwnerB As Single Dim ownerMESSAGE, ownerTITLE As String ownerTITLE = "My Pool" ownerMESSAGE = "Pool1 Enter Share of Owner A" pool1OwnerA = InputBox(ownerMESSAGE, ownerTITLE) ownerMESSAGE = "Pool1 Enter Share of Owner B" pool1OwnerB = InputBox(ownerMESSAGE, ownerTITLE) ownerMESSAGE = "Pool1 Enter Share of Owner C" pool1OwnerC = InputBox(ownerMESSAGE, ownerTITLE) ownerMESSAGE = "Pool1 Enter Share of Owner Pool2" pool1OwnerPool2 = InputBox(ownerMESSAGE, ownerTITLE) ownerMESSAGE = "Pool2 Enter Share of Owner A" pool2OwnerA = InputBox(ownerMESSAGE, ownerTITLE) ownerMESSAGE = "Pool2 Enter Share of Owner B" pool2OwnerB = InputBox(ownerMESSAGE, ownerTITLE) Worksheets("Sheet1").Cells(1, 1).Value = pool1OwnerA + pool1OwnerPool2 * pool2OwnerA Worksheets("Sheet1").Cells(1, 2).Value = pool1OwnerB + pool1OwnerPool2 * pool2OwnerB Worksheets("Sheet1").Cells(1, 3).Value = pool1OwnerC End Sub "this line is already written" close the editor press again design mode button to turn it off. Now, cross your fingers and press the button you have just created. Well, I hope it worked. Bye, for now, Yara "Jeff" wrote: Thank you. What I had in mind was a inputbox to input Pool 1 then the returned values should equal Final Ownership. "Yara" wrote: Hi Jeff I think you need to write a formula instead of VBA macro. Final Ownership, Pool1 Owner A = His share from pool 1 + Share of pool 2 which is 0.25 multiply by his share from Pool 2. ex. Final Ownership Pool 1 Owner A = 0.25 + 0.25*0.5 = 0.375 I hope this will help. "Jeff" wrote: Hi, I start with the initial Ownership of Pool 1 which owns a portion of Pool 2. I need a VBA Macro that would return the final ownership based on the inital ownership. Ownership must equal 1. Initial Ownership Final Ownership Pool 1 Owner A 0.25 Pool 1 Owner A 0.375 Pool 1 Owner B 0.25 Pool 1 Owner B 0.375 Pool 1 Owner C 0.25 Pool 1 Owner C 0.25 Pool 1 Pool 2 0.25 Pool 2 Owner A 0.5 Pool 2 Owner B 0.5 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
Macro help needed | Excel Discussion (Misc queries) | |||
Macro Help needed | Excel Discussion (Misc queries) | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) |