Follow up on code provided by C. Paleo

[K. Georgiadis]

A few weeks ago, Carlos provided me with very useful code that prevented the user from entering quarterly forecasts that, in the aggregate, exceeded the annual budget.

In the meantime, my design has changed, per the attached zip file: there are more product groups, one fewer column (year 2010 has been eliminated) and -- if it makes any difference -- the data sets no longer occur at regular intervals because category headings and subheadings have been added.

I have attempted to adapt the code to the new spreadsheet design but I cannot make it work correctly (because obviously I have screwed something up!). Can you give me a little help?

[Paleo]

Hi K.,

check this out and let me know if it solves your problem.

[K. Georgiadis]

Hi Carlos,

Thanks for the code. A few problems:
1) when I first checked the workbook, I happened to try the last product on the list; it let me exceed the total budget without a protest! However, when I started with Product A and cycled down to it, it did give me the warning
2) When I intentionally enter an excessive amount in year 2007, I also get a message box warning that 2008 also exceeds the budget, even though that is not the case
3) As you go down the list of products, the message box appears several times, with Product A. B. C, etc as the heading, as apparently the code goes through some kind of loop. The number of times the message boxes appear increases as I go down the list

Any suggestions that I may try?

I appreciate your help in spite of fussing about the code!

[Paleo]

Hi K.,

try this one out.

[K. Georgiadis]

Carlos, I didn't try all product modules but I tried most of them at random and this code seems to be working very well. Thank you!

Could you tell me briefly how the code works, especially the statements partaining to the i dimensions (i = 10, etc.). I would appreciate that greatly.

Thanks again

KG

[Paleo]

Hi KG,

dont forget to let me know if it done and to set it solved if it is.

Well I used the Forec variant to hold the columns names and the i to hold the line where your balances are, so as I increment i by 9 each loop you should maintain that as the "distance" between each balance, or update it to show your actual "distance".

At the z variable I set the message you get, by adding each error to the string and then presenting all out of them to you in only one MsgBox.

If you still have any doubt just tell me on which line and I will explain it for you.

[K. Georgiadis]

Got it! I'll mark this solved. Thanks again

Oops, I do have a question...In the case of those modules which are separated by the previous module by more than 9 lines (because of inserted rows for category headings/subheadings), does the code still work?

[Paleo]

Nope, the code is created to run with regular intervals, so if you have irregular intervals you get in trouble. Just try to determine a regular interval which can be 9 or any other quantity of lines, then simply adjust the row "i = i + 9", where the "9" is the regular interval.

[K. Georgiadis]

I understand; it makes sense. Sorry to be a pain but could you also explain i-6 and i-7 in the following lines:

If Range(col & i) < 0 Then
                Ano = Range(col & i - 6)
                z = z & vbCrLf & Range("A" & i - 7) & " - Your quarterly amounts exceed the annual forecast for " & Ano & "."
            End If

Thanks!

[Paleo]

Hi KG,

sure. "i - 7" is to get the Product Name considering your worksheet structure, so if you change the structure you must change this also and "i - 6" is to get the year you are using. Note that I run "i - 7" always on column A because there is where I find your products name and "i - 6" in the same column I am checking your balance to get its year.



I am glad you got it working and feel free to post whenever you have another doubt in it, because its gonna be a pleasure to answer you .

[K. Georgiadis]

I don't understand i-7 The first occurrence of a product name is in A3 and there is a 9 row interval between names.

[Paleo]

Hi K. exactly, the firs occurence is in A3 but my loop starts at A10, so when I run i-7 from A10, I got at A3, and this goes all the way down.

[K. Georgiadis]

Thanks for the clarification! This code is working very well. I just have to make sure to maintain a uniform interval between products, including protection of the sheet so that the user does not insert any rows!

[Paleo]

Exactly, or if you simply cant work with uniform intervals, need to make the code bigger.

[K. Georgiadis]

I'll do my very best to keep the intervals uniform, because otherwise I would have to come back here asking for help! However, if you cared to explain the technique that you would be using to program around irregular intervals, I would be most interested (I am a VBA novice, but extremely curious!)

Since curiosity is my middle name, what does

vbCrLf

stand for?

[Zack Barresse]

vbCrLf = vb Carriage Return, Line Feed

Which basically means a new line. I personally like to use vbNewLine, as that's what it was designed for. You can use them seperately or together, they are both basically the same thing (e.g. vbCr, or vbLf). It's like using Chr(13). It also is a line break (it's the Alt + Enter character), but it's not really the best method to perform such actions.

[K. Georgiadis]

Thanks Zack! You know something, I am really excited and pleased that I can so much timely help on this site, but also a little depressed when I realize that there is so much that I still need to learn!!!

[Zack Barresse]

.. there is so much that I still need to learn!!!

We all do! I learn something everyday here. The day we stop learning is the day we die. And I'm glad that I could be a part of helping you out. I hope someday when I need help I can count on you guys!

[K. Georgiadis]

Hey, I would be delighted to help you out, but it is rather improbable that it would be something related to VBA

[Zack Barresse]

Well, the forum isn't only VBA. And hey, you never know! Never say never! I make bone head mistakes myself - and quite often at that!