Evalute Mismatch Type Problem

[natekris8183]

I have searched online and cannot find anything that addresses this. I know this is a syntax issues of carrying over the formula from the worksheet and appropriately converting certain special characters into the appropriately ASCII code. I placed the text in a messagebox to confirm it's correctness and it appears correct, so I am stumped.

This is the function in the worksheet I want to convert. The aim is to have listboxes populate with a fail safe to exit the loop once I find the last unique value. Just a time saver since there are over 1,000 product codes to cycle through with about 26 unique categories total:

=SUM(IF(FREQUENCY(IF(SKUListTbl[Category 1]=$L$12,MATCH($L$12&SKUListTbl[Category 2],SKUListTbl[Category 1]&SKUListTbl[Category 2],0),""),IF(SKUListTbl[Category 1]=$L$12,MATCH($L$12&SKUListTbl[Category 2],SKUListTbl[Category 1]&SKUListTbl[Category 2],0),""))>0,1))

This was my VBA language synonym to the above formula:

Evaluate("=SUM(IF(FREQUENCY(IF(SKUListTbl[Category 1]=" & Chr(34) & MainCategorySelection & Chr(34) & ",MATCH(" & Chr(34) & MainCategorySelection & Chr(34) & Chr(38) & "SKUListTbl[Category 2],SKUListTbl[Category 1]" & Chr(38) & "SKUListTbl[Category 2],0)," & Chr(34) & Chr(34) & "),IF(SKUListTbl[Category 1]=" & Chr(34) & MainCategorySelection & Chr(34) & ",MATCH(" & Chr(34) & MainCategorySelection & Chr(34) & Chr(38) & "SKUListTbl[Category 2],SKUListTbl[Category 1]" & Chr(38) & "SKUListTbl[Category 2],0)," & Chr(34) & Chr(34) & "))>0,1))")

$L$12 on the sheet will correspond to the variable set in the macro to a preceding listbox value. This is passing through find, as I said, since I see the variable string in the messagebox. Thanks in advance for any thoughts.

 

[RoryA]

Your formula is too long - Evaluate is limited to strings of 255 characters or less.

 

[natekris8183]

Ha, and the funny thing was, I placed it in the worksheet to count the characters. I forgot I have the maincategory unique values dividing into it, so it divided it by 2 and I thought I was ok. If I pass it to a string variable and evaluate the string, would that work in your opinion?

 

[RoryA]

No - the actual evaluated formula is restricted to that length regardless of whether you pass it directly or as a variable.