How to create generic Macro (all variables)

[marcn]

I need a generic macro that can take in three inputs: Yr grey (from droplist choice of 3 or more), Line items rows purple box (droplist- eg. Sales, Costs, Units) and Percentage - yellow box which contains the percentage to increase or decrease the line items the results replace the row.

I am attaching picture and code to date:

Here is code I wrote by kludging other codes together... yes, it is probably pathetic but I am not a VBA programmer and have no time to really learn at this time.

Sub Consult_Monthly_ALL_Yr1()

'declare variables

    Application.Goto Reference:="TCS_ALL_YR1"
Dim ws As Worksheet
Dim rng As Range
Dim myVal As Range
Dim J11 As Integer
Set ws = Worksheets("3a-SalesForecastYear1")
Set rng = ws.Range("tcs_all_yr1")
For Each myVal In rng
If J11 < 100 Then
myVal = myVal.Value * ws.Range("H13")/12
ElseIf J11 > 100 Then
myVal = myVal.Value * ws.Range("H13") + myVal.Value/12
End If
Next myVal
End Sub

Note, the above select a array to multiply 3 rows at once, others use defined name to select single line item (row)

I was thinking of useing input box or userform where I ask for which year, which line items, and percent and that is fed to a generic macro. I know drplist choice can be done in userform... Im open to any idea or solution that reduces the number of separate macros.

thanks
Marc

 

[DataBlake]

sounds like a userform is doable for what you want to do, but you are excluding necessary info like what is in the dropdown, or where on in the workbook the data for said drop down is located.
feel free to be hyper specific as I am not in your profession and do not know what anything on your workbook actually means.
just take us from step 1 to step z in as much detail as you want to give. I'm not the smartest guy in the room so throwing a bunch of numbers and math at me is quite scary.

to fill in the gaps that you may be unaware of VBA can determine things dynamically such as:
the last row of a sheet/range
the last column of a sheet/range
etc

also posting your data using XL2BB would be more beneficial than using a screenshot as its gives us all the information of your sheet that you paste including ranges, formulas, etc