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!

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.

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

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:

=DEC2HEX(H5,6)&"-"&DEC2HEX(I5,6)&"-"&DEC2HEX(J5,6)&"-"&DEC2HEX(K5,6)&"-"&DEC2HEX(L5,6)&"-"&DEC2HEX(M5,6)&"-"&DEC2HEX(N5,6)&"-"&DEC2HEX(O5,6)

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!

Sub B_MakeUniqueNumbers()
    
    Dim cols As Integer
    Dim BaseFormula As String
    Dim UniqueFormula As String
    
    Application.ScreenUpdating = False 'this runs the code withought updating the screen (it runs faster this way)
   
   ' old formula for BaseFormula "DEC2HEX(SUMPRODUCT(H5:O5, 10^{7,6,5,4,3,2,1,0}))"  '(Old formula used)
    
    BaseFormula = "Dec2Hex(H5, 6) & " - " & Dec2Hex(I5, 6) & " - " & Dec2Hex(J5, 6) & " - " & Dec2Hex(K5, 6) & " - " & Dec2Hex(L5, 6) & " - " & Dec2Hex(M5, 6) & " - " & Dec2Hex(N5, 6) & " - " & Dec2Hex(O5, 6)"
    UniqueFormula = BaseFormula
    Dim LastRow As Long
    LastRow = Cells(Rows.Count, "B").End(xlUp).Row 'here we figure out how many rows are used in B column so we can "fill down" the formula
    
    cols = Application.WorksheetFunction.RoundUp((ActiveSheet.UsedRange.Columns.Count - 7) / 8, 0)
    'Above, we count the columns and subtract the first seven cols since they are the address; then round up to the nearest set of 8 cols
    
    
  '  For i = 1 To cols - 1 'this will loop to build the formula based on number of columns
      '  Dim temp As String
       ' temp = UniqueFormula
        'UniqueFormula = temp & " & " & ""DEC2HEX(H5,6)&"-"&DEC2HEX(I5,6)&"-"&DEC2HEX(J5,6)&"-"&DEC2HEX(K5,6)&"-"&DEC2HEX(L5,6)&"-"&DEC2HEX(M5,6)&"-"&DEC2HEX(N5,6)&"-"&DEC2HEX(O5,6)" .Offset(, i * 8).Address(False, False)
  '  Next i
  'old formula DEC2HEX(SUMPRODUCT(" & Range("H5:O5").Offset(, i * 8).Address(False, False) & ", 10^{7,6,5,4,3,2,1,0}))"
    
    'Now the formula is created, and we'll insert it and "Fill down" to the bottom of the sheet
    Range("a5").Offset(, cols * 8 + 8).Select 'Position coursor just outside actively used columns to create formula
    Selection.Formula = "=" & UniqueFormula   'Insert formula
    Range(Selection.Address, Cells(LastRow, cols * 8 + 9)).FormulaR1C1 = Range(Selection.Address).FormulaR1C1 'and "Fill Down" to the last used row

    
    Application.ScreenUpdating = True 'turn back on screen updating
    
    With ActiveSheet
        .AutoFilterMode = False     'clear out any current filters that may be present
        .Range("4:4").AutoFilter    ' and apply our new filter across the 4th row
    End With
End Sub

Hi
Try this in BaseFormula

BaseFormula = "Dec2Hex(H5, 6) & "" - "" & Dec2Hex(I5, 6) & "" - "" & Dec2Hex(J5, 6) & "" - "" & Dec2Hex(K5, 6) & "" - "" & Dec2Hex(L5, 6) & "" - "" & Dec2Hex(M5, 6) & "" - "" & Dec2Hex(N5, 6) & "" - "" & Dec2Hex(O5, 6)"

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!

Sub B_MakeUniqueNumbers()
    
    Dim cols As Integer
    Dim BaseFormula As String
    Dim UniqueFormula As String
    
    Application.ScreenUpdating = False 'this runs the code withought updating the screen (it runs faster this way)
    
    BaseFormula = "Dec2Hex(H5, 6) &  Dec2Hex(I5, 6) &  Dec2Hex(J5, 6) &  Dec2Hex(K5, 6) &  Dec2Hex(L5, 6) &  Dec2Hex(M5, 6) &  Dec2Hex(N5, 6) &  Dec2Hex(O5, 6)"    ' this is the very first portion of the formula (first 8 columns)
    UniqueFormula = BaseFormula
    Dim LastRow As Long
    LastRow = Cells(Rows.Count, "B").End(xlUp).Row 'here we figure out how many rows are used in B column so we can "fill down" the formula
    
    cols = Application.WorksheetFunction.RoundUp((ActiveSheet.UsedRange.Columns.Count - 7) / 8, 0)
    'Above, we count the columns and subtract the first seven cols since they are the address; then round up to the nearest set of 8 cols
    
    
    For i = 1 To cols - 1 'this will loop to build the formula based on number of columns  (TROULBE)
       Dim temp As String
        temp = UniqueFormula
        UniqueFormula = temp & "&" & "Dec2Hex(" & Range("o5").Offset(, i * 1).Address(False, False) & ", 6)"
    
    Next i
    


    'Now the formula is created, and we'll insert it and "Fill down" to the bottom of the sheet
    Range("a5").Offset(, cols * 8 + 8).Select 'Position coursor just outside actively used columns to create formula
    Selection.Formula = "=" & UniqueFormula   'Insert formula
    Range(Selection.Address, Cells(LastRow, cols * 8 + 9)).FormulaR1C1 = Range(Selection.Address).FormulaR1C1 'and "Fill Down" to the last used row

    
    Application.ScreenUpdating = True 'turn back on screen updating
    
    With ActiveSheet
        .AutoFilterMode = False     'clear out any current filters that may be present
        .Range("4:4").AutoFilter    ' and apply our new filter across the 4th row
    End With
End Sub

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

Sub B_MakeUniqueNumbers()
    
    Dim cols As Integer
    Dim BaseFormula As String
    Dim UniqueFormula As String
    
    Application.ScreenUpdating = False 'this runs the code withought updating the screen (it runs faster this way)
    
    BaseFormula = "Dec2Hex(H5, 6) &  Dec2Hex(I5, 6) &  Dec2Hex(J5, 6) &  Dec2Hex(K5, 6) &  Dec2Hex(L5, 6) &  Dec2Hex(M5, 6) &  Dec2Hex(N5, 6) &  Dec2Hex(O5, 6)"    ' this is the very first portion of the forumula (first 8 columns)
    UniqueFormula = BaseFormula
    Dim LastRow As Long
    LastRow = Cells(Rows.Count, "B").End(xlUp).Row 'here we figure out how many rows are used in B column so we can "fill down" the formula
    
    cols = Application.WorksheetFunction.RoundUp((ActiveSheet.UsedRange.Columns.Count - 7) / 1, 0)
    'Above, we count the columns and subtract the first seven cols since they are the address; then round up to the nearest set of cols 
    
    
    For i = 1 To cols - 1 'this will loop to build the formula based on number of columns
       Dim temp As String
        temp = UniqueFormula
        UniqueFormula = temp & "&" & "Dec2Hex(" & Range("o5").Offset(, i * 1).Address(False, False) & ", 6)"
    
    Next i
    

    '    UniqueFormula = temp & " & " & "DEC2HEX(SUMPRODUCT(" & Range("H5:O5").Offset(, i * 8).Address(False, False) & ", 10^{7,6,5,4,3,2,1,0}))"


    'Now the formula is created, and we'll insert it and "Fill down" to the bottom of the sheet
    Range("a5").Offset(, cols * 1.5 + 8).Select 'Position coursor just outside actively used columns to create formula
    Selection.Formula = "=" & UniqueFormula   'Insert formula
    Range(Selection.Address, Cells(LastRow, cols * 1.5 + 9)).FormulaR1C1 = Range(Selection.Address).FormulaR1C1 'and "Fill Down" to the last used row

    
    Application.ScreenUpdating = True 'turn back on screen updating
    
    With ActiveSheet
        .AutoFilterMode = False     'clear out any current filters that may be present
        .Range("4:4").AutoFilter    ' and apply our new filter across the 4th row
    End With
End Sub