DEC2HEX Failing within Macros

Hello Gurus and other excel users like myself,

I have a series of Macros to run on a supplied file and it is not working as it usually does.
Please see attached file:
File for macro - Sheet Prepared.xlsm
I ran the macro A_PrepareSheet to get it to the current state.
From there I run A_RunDistroSort (or if I want to do it step by step to troubleshoot I do these in sequence: B_MakeUniqueNumbers C_ApplyDistros D_FilterDistrosandCopytoNewTabs)
The B_MakeUniqueNumbers Macro inserts the Dec2Hex formula and assigns random numbers to each quantity in H5-M5. The point is to separate each unique string of numbers and assign a Distro number.
When I run the formula, Dec2Hex assigns the same numbers to Spreadsheets 311 and 383 (in column Q), but the numbers in H5-M5 are different.
Therefore Spreadsheets 311 and 383 both end up in Distro 108, but that shouldn't happen because they get different items at different quantities. Help?

Thank you for looking!
Have a great day,
Klotzy

Hello,

I'm still struggling with the problem I mentioned above, so I thought I could come and ask in a different way...
In the attached sheet column Q has a Dec2Hex formula. It is returning the same data even though the cells it evaluates are different.
Can someone explain why?
How can I troubleshoot this?

Thanks very much,
Klotzy

Hi
H213 is 0 and I213 is 10
H215 is 1 and I215 is 0
So:
10^7 *0 +10^6*10 = 10^7*1+10^6*0
The values are equal

Thank you for your reply José Augusto -
I am math illiterate and have been depending on Dec2Hex to find identical strings information. Evidently it's not perfectly suited for that!
Can you suggest something that would be able to output a unique identifier for the items H5 - end of row?
I have a macro that creates the Dec2Hex formula - and would need the solution to be compatible...
Is concatenate something that can be written into a macro with variable rows and columns?
Thanks!

What is the greatest value of each column from H to O?

If your data is smaller than 10 for column H , J , K , L, M, N, O and for column I data is smaller than 99 then you can use this formula
=DEC2HEX(SUMPRODUCT(H5:O5, 10^{8,6,5,4,3,2,1,0}))
to get a unique value.

The value can be in the hundreds - I never know...
Thank you for responding.

Hi Klotzy
You can use other approach if you convert each value and concatenate the result in a string
So
(0 ,10, 0, 0, 0, 0, , ) -> "000000-00000A-000000-000000-000000-000000-000000-000000" and
(1, 0, 0, 0, 0, 0, , ) -> "000001-000000-000000-000000-000000-000000-000000-000000"
Each group of 6 Hexdecimal codes handles integer values to 16,777,215
To do this use in Q5 and copy down

Formula:

Please Login or Register  to view this content.

Note: DEC2HEX(H5,6) accepts decimal value for H5 but use the longint part of the value

Wow - this looks amazing.
I used it on my sample worksheet and it worked great - I have tried to include it in my existing macro, but I get a "type mismatch" error.
Any advice? Code below
I also de-activated the looping part of the macro and I will need to troubleshoot that next... I'm not sure if your formula will be able to be copied down?
Thanks very much!

Please Login or Register  to view this content.

Hi
Try this in BaseFormula

Please Login or Register  to view this content.

Thank you so much, José Augusto.
That did work - and now I'm working on looping to build the formula based on the number of columns.
I'm going to put some more time into working on this part myself - I'll certainly report back (hopefully to post the finished code and mark thread as "solved")

Thanks again! Muito obrigado!

You are welcomed.

I have tried! I am stuck...
I removed the "" - "" from the BaseFormula and I'm working on the Loop.
It will only loop through Row R.
Since this is not direclty related to Dec2Hex anymore, I will be happy to make a new thread if the Mods want!
My Code is below. I attached a file to test it on.
Thank you in advance!

Please Login or Register  to view this content.

I tried more and I have come to a solution.
Thanks again for your help José Augusto.
Have a great day!

Please Login or Register  to view this content.