Hi Need Help Please Help,
Need simplifications as I am NOT a Vba programmer have just kludged together some code that works but is, Im sure, very inefficient. This for a financial proj model where I am using 3 droplist. Year, Line Item, and Percent. They choose each selection and then I have a button to recalculate each line item.
Issue 1. I created defined Names for each line item and group so I start by goto the defined names and that works, but I can’t figure out why I have to then specify the same specific cells that I name in the above array.
Issue 2 As there are dozens of these I need a easy way to connect (switch) so the choose Yr 2, Line items Sales, and Percentage increase., eg. 5% then it will call up the correct row (or rows) for that year and item. Note I have each Year as separate chart (data).
I do not have time to become a VBA programmer as I only use excel occasionally, like this model.
Any help will be greatly appreciated…
Novice
Marc
Need simplifications as I am NOT a Vba programmer have just kludged together some code that works but is, Im sure, very inefficient. This for a financial proj model where I am using 3 droplist. Year, Line Item, and Percent. They choose each selection and then I have a button to recalculate each line item.
Issue 1. I created defined Names for each line item and group so I start by goto the defined names and that works, but I can’t figure out why I have to then specify the same specific cells that I name in the above array.
Issue 2 As there are dozens of these I need a easy way to connect (switch) so the choose Yr 2, Line items Sales, and Percentage increase., eg. 5% then it will call up the correct row (or rows) for that year and item. Note I have each Year as separate chart (data).
I do not have time to become a VBA programmer as I only use excel occasionally, like this model.
Any help will be greatly appreciated…
Novice
Marc
VBA Code:
Sub Consult_Monthly_ALL_Yr1() (NOTE this is example of 3 line items as an array)
'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("c21:N23")
For Each myVal In rng
If J11 < 100 Then
myVal = myVal.Value * ws.Range("H13")
ElseIf J11 > 100 Then
myVal = myVal.Value * ws.Range("H13") + myVal.Value
End If
Next myVal
End Sub