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

 

[Peter_SSs]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Need more efficient VBA code solution -for novice
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide a link, then there shouldn’t be a problem.

 

[marcn]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Need more efficient VBA code solution -for novice
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide a link, then there shouldn’t be a problem.

I understand... new at this.

 

[Peter_SSs]

I understand... new at this.

OK, no problem - just remember for the future.

 

[Fluff]

@marcn
I think you may have missed what Peter said here

If you have posted the question at more places, please provide links to those as well.

 

[marcn]

I could not post the actual link, got auto error telling me I was not allowed. so i placed the name of the forums

 

[Fluff]

I think you are confusing the sites, there is nothing to prevent you from posting a link to this site, pointing to the other sites where you have asked this question.

 

[marcn]

I think you are correct.. very confused... Here you go.

More efficient VBA code For Novice to use - OzGrid Free Excel/VBA Help Forum

Hi and HELP Please! Need simplifications as I am NOT a Vba programmer have just kludgedtogether some code that works but is, Im sure, very inefficient. This for a financial proj model where I am using3 droplists. Year, Line Item, and Percent. They…

www.ozgrid.com

https://www.excelforum.com/exc…r-novice.html#post5288018

I am also further clarifying the code issues since I cannot upload actual WS. Note I did upload them on the ref sites.

Sub Consult_Monthly_ALL_Yr1()
'declare variables
    Application.Goto Reference:="TCS_ALL_YR1"  (I use defined name to select and so if I add rows later the relative positions is maintained)
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")  Yet, I cannot figure the code to refer to the above define name... everything I try does not work (not a vba prog). I can only make it work using actual row position which seems wasteful since I defined the position at the beginning...so confused.
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
As I mentioned and you can see from the attached WS, I have to repeat and modify the code dozens of times to for each combo of Yr, item and percentage.  I have to do the same on on other WS.. I am hoping there is a much more efficient way to do this..
thank again for any suggestions 

I hope that helps clarify.
Marc

 

[marcn]

One more post

More efficient VBA code For Novice to use - OzGrid Free Excel/VBA Help Forum

Hi and HELP Please! Need simplifications as I am NOT a Vba programmer have just kludgedtogether some code that works but is, Im sure, very inefficient. This for a financial proj model where I am using3 droplists. Year, Line Item, and Percent. They…

www.ozgrid.com

 

[Fluff]

Thanks for that, you can refer to a named range like

Set rng = ws.Range("tcs_all_yr1")