Problem with Excel array

It would be helpful if you could attach a sample workbook for us to test on. There isn't anything that jumps out at me with what you have posted right now. The hover peek feature doesn't always work as expected, if the watch shows what is in the value then it's there. Are both values Variant/String?

Yes, both values are showing as Variant/String in each array.

I have attached the workbook for your review. The sub, named "Sales_Detail_by_Customer"  is in the "Detail" module. it is called from the "beforeDoubleClick" event on Sheet5(Sales & Margin by Customer)
2017---GA-Rheem-Equipment-Sales---M.xlsm

I found a problem in the code line:

CustNum1 = CDbl(CustNum)

Select allOpen in new window

The CDbl doesn't exist anywhere else so the CustNum1 is never set. without that, you will never get into the IF to set the value of Results(y,1)

Mike,

That is not the issue. You cannot run the sub directly. You must double click on a line on the "Sales by Customer" worksheet. The "CustNum" comes from that. Look at the code on the double click event and you will see that. I am not having a problem getting to the "if" statement. I get to it fine. For some reason, the only array value that is not working is the Results(y,7), SxData(y,7). When I hover over any of the other array items, the value shows up in the "hover Peek", just not in the aforementioned. Although, Results(y,7), SxData(y,7) do contain values when you do a watch on the arrays. for some reason "SxData(x,7)" shows as empty on the "hover"?

I was able to run your script just fine:
I tried three times with a different Customer each time. Does it not complete for you? Is there an error?

Mike,

No it doesn't. I am not getting an error. The problem is that "Comb1" is empty because Results(y,7) is empty. therefore giving me a wrong answer. If you look at column L on the "Results" worksheet you will see the amount that is used in a calculation to get a true margin on a sale. I am getting no amount when I should be because Comb1 is empty. As you can see, the array Sxdata has a second dimension of 1 to 13. The odd thing is, that I can change SxData(x,7) to any other number but 7 and the concatenation works fine. Go figure?

When I run it, "Comb1" does have a value, but on my tests that value is not found on the SPR Claims sheet. And so that make "Claim" = 0. Every time I am able to check the value of "Results(y,7)" in the hover peek and see that it has a value which then gives it's value to the "Comb1".  

Is there an example customer number that should have the "Claim" be something other than 0?

Mike,

On the "Sales & Margin by Customer" worksheet, column G shows claim back amounts. Any customer with an amount in that column has claims.

Thanks,

Thanks Mike,

That was the issue. I appreciate your help!

Glad we were able to find the problem.