bademployee
Board Regular
- Joined
- Aug 19, 2010
- Messages
- 184
Hi all,
I've almost finished a project and trying to figure out the last component.
On sheet1 I have 6 products in E15:E20 and their prices in AB15:AB20.
I have created userform1:
Step 1. When the form shows I'd like the products in E15:E20 to show under "Product" and prices in AB15:AB20 to show under "Price".
Step 2. The user needs to be able to add a price value in either the "-Disc" and/or "+Fees" fields, from this the adjusted price should show in "New Price"
Step 3. When the user hits "Add", the value under "New Price" needs to show up in AB15:AB20. To complicated this last step (maybe) in cells AB15:AB20 is:
So whatever value is entered into "-Dsc" or "+Fees" needs to be added and/or subtracted from this formula.
I don't know much about vba, the best I could come up with is:
Any help is appreciated.
Mark
I've almost finished a project and trying to figure out the last component.
On sheet1 I have 6 products in E15:E20 and their prices in AB15:AB20.
I have created userform1:
Step 1. When the form shows I'd like the products in E15:E20 to show under "Product" and prices in AB15:AB20 to show under "Price".
Step 2. The user needs to be able to add a price value in either the "-Disc" and/or "+Fees" fields, from this the adjusted price should show in "New Price"
Step 3. When the user hits "Add", the value under "New Price" needs to show up in AB15:AB20. To complicated this last step (maybe) in cells AB15:AB20 is:
Code:
=IF($R15=0,"",IF(ISERROR(SUM(INDEX(Data!$V$3:$Z$114,MATCH($E15,Data!$U$3:$U$114,0),MATCH($B15,Data!$V$1:$Z$1,0))))+
SUM(INDEX(Data!$AO$2:$AS$7,MATCH($T15,Data!$AN$2:$AN$7,0),MATCH($B15,Data!$AO$1:$AS$1,0))),"",
SUM(INDEX(Data!$V$3:$Z$114,MATCH($E15,Data!$U$3:$U$114,0),MATCH($B15,Data!$V$1:$Z$1,0))))+
SUM(INDEX(Data!$AO$2:$AS$7,MATCH($T15,Data!$AN$2:$AN$7,0),MATCH($B15,Data!$AO$1:$AS$1,0))))
So whatever value is entered into "-Dsc" or "+Fees" needs to be added and/or subtracted from this formula.
I don't know much about vba, the best I could come up with is:
Code:
Private Sub cmdclose1_Click()
Unload Me
End Sub
Private Sub cmdadd1_Click()
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
product_1 = ws.Range("E15")
product_2 = ws.Range("E16")
product_3 = ws.Range("E17")
product_4 = ws.Range("E18")
product_5 = ws.Range("E19")
product_6 = ws.Range("E20")
price_1 = ws.Range("AB15")
price_2 = ws.Range("AB16")
price_3 = ws.Range("AB17")
price_4 = ws.Range("AB18")
price_5 = ws.Range("AB19")
price_6 = ws.Range("AB20")
disc.product1.Text = product_1
disc.product2.Text = product_2
disc.product3.Text = product_3
disc.product4.Text = product_4
disc.product5.Text = product_5
disc.product6.Text = product_6
disc.Show
'copy the data to the price cells
ws.Range ("E15") - disc1 + fees1.Value = Me.new1.Value
ws.Range ("E16") - disc2 + fees2.Value = Me.new2.Value
ws.Range ("E17") - disc3 + fees3.Value = Me.new3.Value
ws.Range ("E18") - disc4 + fees4.Value = Me.new4.Value
ws.Range ("E19") - disc5 + fees5.Value = Me.new5.Value
ws.Range ("E20") - disc6 + fees6.Value = Me.new6.Value
Unload Me
End Sub
Any help is appreciated.
Mark