macro to retrieve data from spreadsheet

[bademployee]

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:

=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:

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

 

[bademployee]

bump

 

[bademployee]

To show UserForm1 I have:

Sub userform1_()
userform1.Show


Dim ws As Worksheet
Set ws = Worksheets("Main")


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")
    
End Sub

The first time I ran it, the value in E15 showed in the first field under Product. Can't get it to repeat....what am I missing?

 

[bademployee]

ok, Step 1 is almost done:

Sub disc_()
disc.Show

Dim ws As Worksheet
Set ws = Worksheets("Main")

products1 = ws.Range("E15")
products2 = ws.Range("E16")
products3 = ws.Range("E17")
products4 = ws.Range("E18")
products5 = ws.Range("E19")
products6 = ws.Range("E20")

prices1 = ws.Range("AB15")
prices2 = ws.Range("AB16")
prices3 = ws.Range("AB17")
prices4 = ws.Range("AB18")
prices5 = ws.Range("AB19")
prices6 = ws.Range("AB20")

disc.product1.Text = products1
disc.product2.Text = products2
disc.product3.Text = products3
disc.product4.Text = products4
disc.product5.Text = products5
disc.product6.Text = products6

disc.price1.Text = prices1
disc.price2.Text = prices2
disc.price3.Text = prices3
disc.price4.Text = prices4
disc.price5.Text = prices5
disc.price6.Text = prices6

End Sub

Above opens the form and displays the products and prices like so:



Last issue for Step 1 - how do I refresh the form?....I'm having to launch the form twice to see the correct products and prices from my spread sheet.

 

[bademployee]

Sorted, added a Calculate / Refresh button:



Step 2.

Just concentrating on the top row of the form:

-Disc = disc1
+Fees = fees1
New Price = new1
Price = price1
price1 = sheet("Main").Range("AB15")

The following formula is in AB15:

=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))))

Typically when a user opens the form a product and price will be displayed, what code is required to calculate a value in "new1", if there are values in "disc1" and/or "fees1" ?

Grateful for any help....

 

[bademployee]

Solved