Macro to allow user to automate combining pricing

[KlayontKress]

To all,


I'm looking for a macro that will reduce a lot of tedious manual calculating. We have a program that exports pricing for customers, but the pricing comes out as individual options that are normally purchased together. The customer wants to see these values combined which is resulting in all of the manual work to do this. What we have to do is add the Add on price to the Base, Option 1, Option 2, Option 3, etc.. What I'd like to be able to do is have a macro to prompt the user for the values to add to the rest of the items (column E), and then prompt the user to select the columns to add this value to (columns D, F, G, H Below). Then the macro would add the values in column E to the items in the respective rows for columns D, F, G, H, etc.

A	B	C	D	E	F	G	H
1			Base	Add on 1	Option 2	Option 3	Option 4
2		Product 1	30	5	40	50	60
3		Product 2	60	11	70	80	90
4		Product 3	90	15	100	110	120
5
6			new values after macro runs
7			35	5	45	55	65
8			71	11	71	81	91
9			105	15	115	125	135
10

 

[maabadi]

if your first number is in D2 then this working.
I limit numbers between 0 to 54, you can change it.

Sub AddValuesToProducts()
Dim a As Long
Dim b As Long
Dim c As Long
Dim i As Long


Application.ScreenUpdating = False
 
 Do
  a = Application.InputBox(Prompt:="Input Values to add Product1", Default:="Enter only number between 0 and 54 here", Type:=1)
 If Not IsNumeric(a) Then
    MsgBox "You must enter a numerical value."
 
    ElseIf a < 0 Or a > 54 Then
    MsgBox "Only between 0 and 54 may be added."
 End If
 Loop Until a > 0 And a < 54
 
 Do
 b = Application.InputBox(Prompt:="Input Values to add Product2", Default:="Enter only number between 0 and 54 here", Type:=1)
 If Not IsNumeric(b) Then
    MsgBox "You must enter a numerical value."
 
    ElseIf b < 0 Or b > 54 Then
    MsgBox "Only between 0 and 54 may be added."
 End If
 Loop Until b > 0 And b < 54
 Do
 c = Application.InputBox(Prompt:="Input Values to add Product3", Default:="Enter only number between 0 and 54 here", Type:=1)
If Not IsNumeric(c) Then
    MsgBox "You must enter a numerical value."
 
    ElseIf c < 0 Or c > 54 Then
    MsgBox "Only between 0 and 54 may be added."
 End If
 
Loop Until c > 0 And c < 54

Range("E2").Value = a
Range("E3").Value = b
Range("E4").Value = c

For i = 4 To 8
Cells(2, i).Value = a + Cells(2, i).Value
Cells(3, i).Value = b + Cells(3, i).Value
Cells(4, i).Value = c + Cells(4, i).Value
If i = 4 Then
i = i + 1
End If

Next i

Application.ScreenUpdating = True
End Sub