VBA to return corresponding values off serial numbers

[Hillster]

I have attached a spread sheet where I need vba that can read serial numbers and return 2 digit results based on the value in a single cell (D1). Cell D1 with yellow highlight will be where a number gets inputted by the user, then the vba will calculate all of the serial numbers below in column D, and delivering the results in column E...It might not even be possible to make vba understand what it is supposed to be calculating... I suppose the person writing the vba needs to understand it first... but any help would be greatly appreciated...I know there will be alot of questions so i will answer them as best I can...

[Keebellah]

As you mention it yourself, a third party needs to understand what you want to understand.
VBA can do many things as long as you tell it what how and where you want it.
Also, when you explain things is a worksheet why not just insert a text box and type the text in makes it easier to read and you van highlight and underline everything youwant.
So.... back to your drawing board and explain it a a way somebody with no is idea whatsoever of what you want done can understand it.
And then... I'm sure one of us can pick it up.
I've returned your file with a textbox showing what I meant.

[Hillster]

Keebelllah, thank you for your suggestions and direction. I have re-attached a copy of what i need assistance with, including what i hope to be a much better
presentation of whats needed per your suggested layout... I hope this will suffice for a start of productive dialog and hopefully a solution.

[Keebellah]

Hi, I'll have to reread to really understand it. You see that the textboxes make it easier to illustrate and explain.
So give me some time to wrap my thoughts around this.
The increase and all that. it has to sink in.

[Hillster]

Yes, you are exactly correct regarding the text boxes, etc.... each "serial number" / code is really quite simple in themselves as they are basically a visual representation of how you might verbally present what you see when a value exists in front of you and then asked to come up with a speech to text style explanation of what is going on with the values presented to you.... You are probably going to want to scream when i attempt this crazy explanation but here goes... I am only interested in a result of 2 digits so just stay focused as i attempt this...
Example:

if you have two sets of values: 12
404

Explain how far <=> the bottom three values are from the two above.... one at a time, example: the 1st position (4) is 3 digits > than the (1) and they are both in the 1st position when reading from left to right, then again with the 1st position same number (4) will now be compared to the 2nd position in value above (2)... the (4) is two digits higher than the (4)
We continue checking digits as compared between the three digit values below the set above and create an explanation of there positional and numerical differences, which creates a "serial number" looking result. So I always start with the 1st position of the lower and upper row then onto the second and finally the 3rd position.

a final result of the above can deliver several results but as soon as two parts are accomplished i stop calculating, as per my examples in the attached spread sheet. so the above would look like this....1O1D31O2D2... so the 1O1 is the 1st position of the number in lower row (4), the "O" is literally a letter, not a zero, it means "Off" so 1"off" to 1 (position) and because the 1 is 3 digits lower or down than the 4 i use the letter "D". so you see 1O1D3... The second half i need is the 1O2... again the positional part of the calculation.. 1st position "Off" to the 2nd position (4 compared to 2), and because the 2 is two digits lower or down than the 4 i use the letter "D" again...so you see 1O2D2... If the numbers were lower by only one digit i would only use the letter "D" with no number incremented after it (D2, etc), and if the digits were higher, then i use the opposite of down (D) and instead use (U) or U2, U3.... Finally if then number 4 of "404 was instead a 1, it would match the 1 in the row above, then you will see that displayed in several serial number in my attached spread sheet as a "C" simply meaning the 1 (in this example) carried over to the value above.... Very Very confusing to attempt explaining and as

[Keebellah]

I've read it but it's not making sense to me, I can't seem to grasp what you're looking for and honestly, I just don't understand it.
And this makes it hard to work with and write any code or design any formulas to accomplish what you want.
I must be very infuriating for you that you've explained it all and the other person doesn't grasp the idea, but the thing is I do not see the logic at all.
I added several columns and in A and B split the 'combination' into the left 3 three which I am assuming are a kind of key and in B the remaining digits.

I don't understand the Row(2) and 4 an all that, yes, I noticed the second digit in A is not a digit but a letter.
The positioning is what is not clear at all.

So, I hope you can bring up the patience to explain it again with maybe showing the the columns next to this what it what and what leads to what number.
I do not see where the result of 50 or other comes from.
Sorry for being so hardheaded and dense understanding.

[Rick Rothstein]

I think I understand what you want, but I am not sure your sample is correct in all instances. For example, with your seed value (cell D1) equalling 377, you have 08 as the result for 1O1U23O2U but I think it is wrong.. I think the correct value is 58. If I am right, the 1O1 means the 3 stays in the first position and the U2 means increase it by 2 making it 5, not 0 (right digit of 10 where you apparently added 3 to one of the 7s)... I agree with 8 as the last digit. As a second example, you have 09 as the result for 2O1U3O2U2. If I understand correctly, that 3 does not belong for the "first group", rather, I believe there is an implied 1 after the "U" because there is only one digit between the "U" and the "O". If that is correct, then the first number is 7 plus 1 for the first digit, not 0. Am I correct on this? If so, then I think I have a macro solution for you; however, if I am not correct, then you need to expand your description of how to interpret your codes.

[Hillster]

Rick, You are correct about the "08" in E13... That should be 58, this is a perfect example of needing a VBA to go through all this data to produce the result as it is so susceptible to errors by doing it manually. in regards to the 09, you are also correct. I purposely did not mention there are only ten digits being used (0 thru 9) in all possible values, and i did so because it would have caused even more confusing but you are exactly on point with "getting it"...and you are interpreting the "serial numbers" / codes correctly... the 2O1U3O2U2 should be 89. So the logic of the available numbers / values / digits are counted as follows... 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 0, 1.... etc. So when a 9 is in the row below a zero (0), it is interpreted the 0 is one digit higher / "U" from the 9.. reverse of that is when a Zero (0) is in the row below a 9 it is interpreted that the 9 is one digit lower than the zero... A good question / observation could be mentioned..."what if you have an 8 in the row below a zero?" Answer: 8+2 digits higher= 0, how about a 7 in the row below a Zero? Answer: 7+3 digits higher = 0, and last piece, any variation of calculations is the fact nothing is calculated past 3 digits higher (U) or lower (D) than any other value in the row below... So the only criteria calculated is "D3, D2, D (which equals 1 digit down/lower), C (which means equal to value in row below and carry's over to either 1st or 2nd position of the row above., then for higher / "U" we have: "U, U2, U3"... i SURE hope this helps clarify even more....

[Hillster]

Keebellah, Maybe the explanation in my reply to Rick Rothstein will assist and his explanation of his observations which are exactly correct... Let me know if it in fact does help.

[Rick Rothstein]

Here is the macro I came up with which I believe correctly calculates the two digit numbers that should go in Column E and the frequencies that should go into Column F...

Please Login or Register  to view this content.

NOTE: I just read your first message again and note that you want code to calculate the serial number codes in Column D. Is that correct? Note that the above code assumes those serial number codes are already in Column D and it only calculates the two-digit number and frequencies that go into Column E and F.

[Keebellah]

I think Rick has a solution, have copied the code to your sample and will see if it sheds more light

[Hillster]

Rick Rothstein, Absolutely brilliant! This works perfectly, accurately and fast! I was truly not expecting anyone to understand what in the world i was trying to explain. I am very thankful for you and your help as well as Keebellah's input and help on presenting my setup in a more digestible way. This is a huge step forward. In regards to your last Note, regarding the 1st two positions is a 3rd position calculation to the top row instead of only 2 would be way to much work manually, now maybe i can use this VBA and it will calculate a 3rd position portion of the serial numbers?.... i had them setup that way originally but removed the 3 position for the reason i just stated, way to difficult to manually calculate all of that info and get it right... Thank you again!!! Amazing work!!!

[Keebellah]

Well, mow you have the starting point to build on.
VBA is nothing more than a series of step-by-step sequential instructions that only do what YOU tell it to do respecting the correct syntax.
Imagination is by long the only limit and all you really need (lot of) is time and patience (for troubleshooting)
@Rick: looks great

[Hillster]

Well said Keebellah!!

[Hillster]

Rick i have attached the spread sheet showing the same serial number structure you programmed but the ones pertaining to the 3rd position in the "row above" is what the VBA is not picking up...
I was wondering if you could take a look and see what it needs done in order to calculate properly? The VBA keeps getting a debug error...I sure appreciate your
help!!

[Rick Rothstein]

Rick i have attached the spread sheet...

When I click the link, the forum responds saying "Invalid Attachment specified". Can you try to upload it again.

[Keebellah]

@Rick: no problems here

[Rick Rothstein]

@Rick: no problems here

I don't know why, but yesterday I kept getting errors, today it worked.

Rick i have attached the spread sheet showing the same serial number structure you programmed but the ones pertaining to the 3rd position in the "row above" is what the VBA is not picking up...

I am not sure what you mean by "in the 'row above'", but extending what I did to output three characters instead of two yields the following code... does it produce the output you want?

Please Login or Register  to view this content.

[Hillster]

Rick, i pasted your code into my worksheet and this is what happens when i click the form control button to get results? Am i doing something incorrectly or...?

[Rick Rothstein]

Rick, i pasted your code into my worksheet and this is what happens when i click the form control button to get results? Am i doing something incorrectly or...?

It was 3:00 in the morning when I posted my code and I forgot to mention that you have some "old" two-digit data in your list starting at Row 3059... there is no third digits to process from that row downward and my code, as currently written, requires there to be three digits to process. Assuming leaving them in the list was a mistake, I simply deleted them. If that was not correct and you could have a mixture of two- and three-digit results, let me know and I'll adjust the code accordingly, otherwise I think my code will do what you want once you remove those values.

[Hillster]

Rick, it appears to be working perfectly!! Thank you so much... I would love to get up with you on some other project tid bits... is that possible?

[jolivanes]

See Forum Rules #8.

[Hillster]

Rick, it apparently i made and oversight as when i input a value in D1 if it leads with a Zero or two zeros, Example: (086, 004) i get a debug error. I think it has something to do with the leading zero's?

[Rick Rothstein]

Rick, it apparently i made and oversight as when i input a value in D1 if it leads with a Zero or two zeros, Example: (086, 004) i get a debug error. I think it has something to do with the leading zero's?

Saying "debug error" does not tell us what the error was. When the error message displayed, what was the error number and error description in the message box?

A quick guess is that your number in cell D1 does not actually have leading zeroes... if you did not change the cell's format to Text, then your 086 or 004 went into the cell with an actual value of 86 or 4 meaning there was not 3 digits for my code to read. So, is cell D1 formatted as Text?

[Hillster]

Rick, i apologize for not being more specific about the "Debug Error" i feel so stupid when it comes to VBA and it frustrates me so, my fault for being so unclear
i have tried all different formats (General, custom, text, number), Leaving it the way it was, etc. It doesn't stop the error,as you know the user enters values in
D1 and many values will lead with a zero or two zero's or it's possible for 000. Anyway, i hope the attached screenshot will help?

[Keebellah]

What's the total lengthof the string being tested?
Run though the VBA code step by step and you can check the values passed at every step

[Rick Rothstein]

i have tried all different formats (General, custom, text, number), Leaving it the way it was, etc. It doesn't stop the error,as you know the user enters values in
D1 and many values will lead with a zero or two zero's or it's possible for 000.

I have modified the code and I think it should automatically eliminate the problem. Give it a try and let me know.

Please Login or Register  to view this content.

[Hillster]

Rick, working perfectly!! You are incredible with this VBA stuff!! Thanks for all of your help!