Need More Efficient VBA code - Novice

[marcn]

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

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

 

[marcn]

Wow! than worked perfectly.... not sure how I missed it in the 100 times I tried but thanks.

If I can impose further, any ideas on how to modify the code so it is more universal as I will have probably 50 sub macros. if I can have one that is more variable driven that I can call for each combination of Yr, Line item (similar to how the percentage variable works in all).

again thank you so much... I probably spent 5 hours or more and could not resolve it.
marc

 

[Fluff]

I can't help with the rest of the code, because I don't understand what it's meant to be doing.
For instance J11 never has a value, so you will never get to the ElseIf

 

[marcn]

I understand. Since I cannot upload WS (from my understanding) I am uploading photo... note this is just one year, there are 3 years and this is same for other expense and reve items on additional WS. I was thinking if I use a userform and it just asks user to specify the line item(s) and yr and percentage it would place them in a single macro..

Image... Year (gray is droplist) Purple line items is droplist and yellow is just value (cell value)

does any of this help?

thanks for all you assistance

Marc

 

[Fluff]

Whilst it's possible to assign a macro to multiple different buttons & to know which button was clicked, we would need to know exactly what should happen dependant on what values were selected from the dropdowns.

As the first part of your problem has been solved, I would suggest you start a new thread for this next part giving exact details

 

[marcn]

thanks will do.

 

[Fluff]

You're welcome