Double-quote Syntax Error in Evaluate VBA

[natekris8183]

I have the following data set. Certain values in the [SKU] have a double-quote (chr(34)) denoting the display size (e.g. 7"). When using the VBA code below, I get a type mismatch when I come to a SKU with a double-quote since I have to input the Chr(34) manually into the Evaluate function. Not sure if anyone has a work around. I cannot manipulate the data with a Replace for data integrity sake, since this moves over into POs and other systems it has to remain the same SKU. Essentially trying to see if there is a way it to not break the text string in the Evaluate function when the SKU possess a double-quote as a part of the text string. Thanks!

Excel 2012
	A	B	C	D	E	F	G
1	Week	SKU	SKU Status	Source	Actual	Trend	Forecast
2	1/5/15	SKU1	Active	Dealer	19
3	1/5/15	SKU2	Active	Dealer	13
4	1/5/15	SKU3	Active	Dealer	157
5	1/5/15	SKU4	Active	Dealer	51
6	1/5/15	SKU5	Active	Dealer	123
7	1/5/15	SKU6	Active	Dealer	5
8	1/5/15	SKU7	Active	Dealer	70
9	1/5/15	SKU9	Active	Dealer	27
10	1/5/15	SKU16	Active	Dealer	3
11	1/5/15	SKU18	Active	Dealer	3
12	1/5/15	SKU19	Active	Dealer	20
13	1/5/15	SKU20	Active	Dealer	3
14	1/5/15	SKU21	Active	Dealer	3
15	1/5/15	SKU22	Active	Dealer	2
16	1/5/15	SKU23	Active	Dealer	5
17	1/5/15	SKU24	Active	Dealer	6
18	1/5/15	SKU29	Active	Dealer	1
19	1/5/15	SKU30	Active	Dealer	2
20	1/5/15	SKU35	Active	Dealer	6
21	1/5/15	SKU36	Active	Dealer	1
22	1/5/15	SKU37	Active	Dealer	1
23	1/5/15	SKU51	Active	Dealer	1
24	1/5/15	SKU52	Active	Dealer	2
25	1/5/15	SKU54	Active	Dealer	1
26	1/5/15	SKU61	Active	Dealer	6
27	1/5/15	SKU68	Active	Dealer	126
28	1/5/15	SKU69	Active	Dealer	109
29	1/5/15	SKU70	Active	Dealer	63
30	1/5/15	SKU71	Active	Dealer	172
31	1/5/15	SKU72	Active	Dealer	18
32	1/5/15	SKU73	Active	Dealer	271
33	1/5/15	SKU74	Active	Dealer	157
34	1/5/15	SKU75	Active	Dealer	406
35	1/5/15	SKU76	Active	Dealer	13
36	1/5/15	SKU77	Active	Dealer	18
37	1/5/15	SKU78	Active	Dealer	38
38	1/5/15	SKU79	Active	Dealer	3
39	1/5/15	SKU80	Active	Dealer	16
40	1/5/15	SKU81	Active	Dealer	23
41	1/5/15	SKU82	Active	Dealer	445
42	1/5/15	SKU83	Active	Dealer	529
43	1/5/15	SKU84	Active	Dealer	367
44	1/5/15	SKU85	Active	Dealer	336
45	1/5/15	SKU86	Active	Dealer	129
46	1/5/15	SKU87	Active	Dealer	329
47	1/5/15	SKU88	Active	Dealer	18
48	1/5/15	SKU89	Active	Dealer	3
49	1/5/15	SKU90	Active	Dealer	36
50	1/5/15	SKU91	Active	Dealer	60
51	1/5/15	SKU92	Active	Dealer	81
52	1/5/15	SKU93	Active	Dealer	105
53	1/5/15	SKU94	Active	Dealer	91
54	1/5/15	SKU95	Active	Dealer	18
55	1/5/15	SKU96	Active	Dealer	80
56	1/5/15	SKU121	Active	Dealer	1
57	1/5/15	SKU124	Active	Dealer	3
58	1/5/15	SKU133	Active	Dealer	276
59	1/5/15	SKU134	Active	Dealer	85
60	1/5/15	SKU137	Active	Dealer	140
61	1/5/15	SKU138	Active	Dealer	2
62	1/5/15	SKU140	Active	Dealer	55
63	1/5/15	SKU144	Active	Dealer	14
64	1/5/15	SKU145	Active	Dealer	4
65	1/5/15	SKU147	Active	Dealer	7
66	1/5/15	SKU148	Active	Dealer	25
67	1/5/15	SKU153	Active	Dealer	3
68	1/5/15	SKU154	Active	Dealer	117
69	1/5/15	SKU161	Active	Dealer	114
70	1/5/15	SKU162	Active	Dealer	10
71	1/5/15	SKU165	Active	Dealer	30
72	1/5/15	SKU167	Active	Dealer	2
73	1/5/15	SKU168	Active	Dealer	1
74	1/5/15	SKU171	Active	Dealer	201
75	1/5/15	SKU173	Active	Dealer	168
76	1/5/15	SKU174	Active	Dealer	3
77	1/5/15	SKU175	Active	Dealer	40
78	1/5/15	SKU179	Active	Dealer	5
79	1/5/15	SKU185	Active	Dealer	7
80	1/5/15	SKU188	Active	Dealer	60
81	1/5/15	SKU189	Active	Dealer	90
82	1/5/15	SKU192	Active	Dealer	3
83	1/5/15	SKU193	Active	Dealer	214
84	1/5/15	SKU194	Active	Dealer	76
85	1/5/15	SKU195	Active	Dealer	93
86	1/5/15	SKU197	Active	Dealer	73
87	1/5/15	SKU205	Active	Dealer	13
88	1/5/15	SKU207	Active	Dealer	11
89	1/5/15	SKU208	Active	Dealer	3
90	1/5/15	SKU209	Active	Dealer	1
91	1/5/15	SKU210	Active	Dealer	344
92	1/5/15	SKU211	Active	Dealer	145
93	1/5/15	SKU212	Active	Dealer	31
94	1/5/15	SKU214	Active	Dealer	9
95	1/5/15	SKU215	Active	Dealer	105
96	1/5/15	SKU216	Active	Dealer	53
97	1/5/15	SKU217	Active	Dealer	349
98	1/5/15	SKU219	Active	Dealer	91
99	1/5/15	SKU234	Active	Dealer	1
Raw Data

Function VBA_Trend()

Dim tbl As ListObject
Dim curDate As Date
Dim xRange, yRange, xNew As Range
Dim curTrend
Dim weekRng As Range
Dim SKURng As Range
Dim sourceRng As Range
Dim curDate1 As Integer

Application.ScreenUpdating = False

Set tbl = ActiveSheet.ListObjects("RawSalesDataTbl")
Set weekRng = ActiveSheet.Range("RawSalesDataTbl[Week '#]")
Set SKURng = ActiveSheet.Range("RawSalesDataTbl[SKU]")
Set sourceRng = ActiveSheet.Range("RawSalesDataTbl[Source]")
 
For i = 2 To tbl.Range.Rows.Count

    curDate = Cells(i, Range("RawSalesDataTbl[Week '#]").Column).Value
    prevdate = Evaluate("=DATEVALUE(" & Chr(34) & DateAdd("d", -2 * 7, curDate) & Chr(34) & ")")
    curSKU = Cells(i, Range("RawSalesDataTbl[SKU]").Column).Value
    curSource = Cells(i, Range("RawSalesDataTbl[Source]").Column).Value
    
    
    If WorksheetFunction.CountIfs(Range("RawSalesDataTbl[Week '#]"), "<=" & prevdate, Range("RawSalesDataTbl[SKU]"), "=" & curSKU, Range("RawSalesDataTbl[Source]"), "=" & curSource) > 0 Then

            rNum = WorksheetFunction.CountIfs(Range("RawSalesDataTbl[Week '#]"), ">=" & prevdate, Range("RawSalesDataTbl[Week '#]"), "<" & curDate, Range("RawSalesDataTbl[SKU]"), "=" & curSKU, Range("RawSalesDataTbl[Source]"), "=" & curSource)
            
            mindate = Evaluate("=MIN(IF(RawSalesDataTbl[Week '#]<=" & prevdate & ",IF(RawSalesDataTbl[SKU]=" & Chr(34) & curSKU & Chr(34) & ",IF(RawSalesDataTbl[Source]=" & Chr(34) & curSource & Chr(34) & ",RawSalesDataTbl[Week '#]))))")
            fRow = Evaluate("=MATCH(" & Chr(34) & mindate & curSKU & curSource & Chr(34) & ",RawSalesDataTbl[Week '#]&RawSalesDataTbl[SKU]&RawSalesDataTbl[Source],0)")
        
            For Each c In Range(Cells(fRow, Range("RawSalesDataTbl[Week '#]").Column), Cells(i, Range("RawSalesDataTbl[Week '#]").Column))
    
                If c.Value >= DateAdd("d", -2 * 7, curDate) Then
                
                    If c.Value < curDate Then
                
                        If Cells(c.Row, Range("RawSalesDataTbl[SKU]").Column).Value = curSKU Then
                                              
                            If Cells(c.Row, Range("RawSalesDataTbl[Source]").Column).Value = curSource Then
                        
                                j = j + 1
                            
                                If j <= rNum Then
                                                    
                                    If j < rNum Then
                                    
                                        Cells(rNum - (rNum - (j + 1)), Range("'Raw Sales Data'!$T:$T").Column).Value = Cells(c.Row, Range("RawSalesDataTbl[Week '#]").Column).Value
                                        Cells(rNum - (rNum - (j + 1)), Range("'Raw Sales Data'!$U:$U").Column).Value = Cells(c.Row, Range("RawSalesDataTbl[Actual]").Column).Value
                                
                                    Else
                                   
                                        If j = rNum Then
                                    
                                            Application.ScreenUpdating = True
                                                                            
                                            Cells(rNum - (rNum - (j + 1)), Range("'Raw Sales Data'!$T:$T").Column).Value = Cells(c.Row, Range("RawSalesDataTbl[Week '#]").Column).Value
                                            Cells(rNum - (rNum - (j + 1)), Range("'Raw Sales Data'!$U:$U").Column).Value = Cells(c.Row, Range("RawSalesDataTbl[Actual]").Column).Value
                                            Cells(rNum - (rNum - (j + 2)), Range("'Raw Sales Data'!$T:$T").Column).Value = curDate
                                            Set xRange = Range(Cells(2, Range("'Raw Sales Data'!$U:$U").Column), Cells(rNum - (rNum - (j + 1)), Range("'Raw Sales Data'!$U:$U").Column))
                                            Set yRange = Range(Cells(2, Range("'Raw Sales Data'!$T:$T").Column), Cells(rNum - (rNum - (j + 1)), Range("'Raw Sales Data'!$T:$T").Column))
                                            Set xNew = Range(Cells(j + 2, Range("'Raw Sales Data'!$T:$T").Column), Cells(rNum - (rNum - (j + 2)), Range("'Raw Sales Data'!$T:$T").Column))
                                            Cells(i, Range("RawSalesDataTbl[Trend]").Column).Value = Application.WorksheetFunction.Trend(xRange, yRange, xNew)
                                            
                                            
                                            Range(Cells(2, Range("'Raw Sales Data'!$T:$T").Column), Cells(j + 2, Range("'Raw Sales Data'!$u:$u").Column)).Clear
                                            j = Clear
                                            xRange = Clear
                                            yRange = Clear
                                            xNew = Clear
                                        
                                        End If
                                       
                                    End If
                            
                                End If
                            
                            End If
                    
                        End If
                    
                    End If
            
                End If
        
            Next c
            
    Else
               
        Cells(i, Range("RawSalesDataTbl[Trend]").Column).Value = Cells(i, Range("RawSalesDataTbl[Actual]").Column).Value
               
    End If
                                  
Next i

Application.ScreenUpdating = True

 

[LockeGarmin]

Make a new variable:

curSKUeval = Replace(curSKU, Chr(34), Chr(34) & Chr(34))

Then use that variable every where you use "Evaluate()" and use curSKU where you don't.

 

[natekris8183]

I actually tried that. The match function locates no matches because the SKU in the worksheet contains the double quote. That's the complication. For now the work around was to create a "Clean SKU" column in the table set that strips the double quote from the SKU text and match to that row instead of the [SKU] column. It's not ideal though. I am not a fan of creating erroneous columns to hold qualifying data sets. Just because it slows the introduction of new data into the table. So if you have a thought on how to incorporate the string containing the double quote into the evaluate without replacing the Chr(34), I'd be all open ears. I did locate a way to see if the string contains the double quote through the countif and utilizing the Chr(34) with a wild card "*" qualifier. So at least I can skip over them for now in the macros.

 

[LockeGarmin]

Hmm, not sure what the issue is. When I test it out on how I imagine the data looks the method works fine. Maybe there is another character beside the double quote we aren't seeing? If I input data in A1=1" Bar, A2=2" Bar, and A3=3" Bar, and the run the following line I don't get any errors. Sorry I can't be much more help then that.

Evaluate("=MATCH(" & Chr(34) & "2" & chr(34) & chr(34) & " Bar" & chr(34) & ",A1:A3, 0)")

 

[natekris8183]

A better example would be let's say your string yielded a result that said 128G 7" Samsung Tablet. The double quote inherent in the thread you are pulling the string from has a double quote. A simple match function in the worksheet where the referenced cell contains the same string works because it's set to the object whereas the VBA worksheetfunction.Match pulls the string through as text thereby breaking the text string into two parts. I had through the about deconstructing the string to 3 parts, where the string before the double quote is a standalone string as well as the double quote and finally the last portion of the string after the double quote. Just creates abother level of complexity to the runtime though. So I thought maybe I should be referring to the text string as something other than a string, although I wouldn't know what.

 

[LockeGarmin]

Oh, wait a second. I'm not seeing in your code where you use WorksheetFunction.Match. But if you are using WorksheetFunction.Match then you should be using the curSKU variable and not the curSKUeval variable.

 

[DocAElstein]

Hi,
I have not fully been able to break down your code and see exactly what the problem is. It would maybe help to try to isolate the problem in a simple Code example.
I may have missed the point and the following is going off at a tangent. Apologies if that is the case.
But what i have tried to do is give a simple case where strings containing the odd quote or two put a “Spanner in the works” in Evaluate, and I show a way to get over it. It might be that this is exactly what you don’t want.. but at least that will make that a bit clearer if nothing else!!

This is my start point:

[Table="width:, class:head"][tr=bgcolor:#888888][th]_Row\^Col[/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

40​

[/td][td]SKU[/td][td]BeDu[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

41​

[/td][td]SKU"[/td][td]BeDu[/td][/tr]
[/table]

And I assume a simplified exampple of using the Evaluate to concatenate the two values in the rows and give them out in columns D and E

There are three parts to the code.

Parts 1 and 2 are similar . Each deals with a row

In part 3 both rows are done in the same line ( or would be if I had not opened the code up a bit for demo purposes )

So to demo it, Make that Range or a similar one and run the code, ( preferably in Debug F8 ) and hover over the variables as they are filled and you see what is going on....... To make the correct syntax for the evaluate two quote where they appear are changed ( within the strEval that VBA sees) to three quotes

The idea is you have your whole String, strEval, built up first.
Then instead of doing

= Evaluate(strEval)

You do this

=Evaluate(Replace(strEval, """""", """"""""))

It works for the simple cases I tried. Note for a quote appearing within a string it would not work,... but more of the same with a bit of modifying of the basic Replace idea could be done to catch and change them appropriately

You are not actually manipulating the data, that is to say the String you put in the evaluate. You simply modify the Evaluate, sort of, making it evaluate a slightly mofified string.
The modification does no harm usually if it is not needed. But if there are any strings with a “ or two at the end of a String, as in your original description, then this mod should change them appropriately so that the Final Evaluate will work and preserve the Quotes as you had them. I think in Worksheet Functions with String arguments inside a Evaluate the basiic princilple may still apply.

And note you do not need the “ bodge “ at all when you reference the cell with a Range.Address bit

Maybe just some ideas that might be of use along the way.
Interesting thread, I never thought of playing around with, chr(34) instead. Must get around to looking at that...


Alan

P.s. Quotes in VBA and in particular with Evaluate, give me a bad headache every time I look at it.
Ref
http://www.mrexcel.com/forum/excel-questions/696820-quotes-visual-basic-applications.html
Special concatenation - Page 3

Here is the Demo Code: ( It should work on the Active Sheet )
(Sub ScooBDooDumpsInRowsAndRangesWithQuotesWonks() )

[color=darkgreen]'[/color]
[color=blue]Sub[/color] ScooBDooDumpsInRowsAndRangesWithQuotesWonks() 'http://www.mrexcel.com/forum/excel-questions/921049-double-quote-syntax-error-evaluate-visual-basic-applications.html

Rem 1) [color=darkgreen]'ScooBDoo Dumps in Row 40[/color]
[color=blue]Dim[/color] RstEvalB40 [color=blue]As[/color] [color=blue]String[/color] [color=darkgreen]'Expecting a final String result for concatenating cell values using Evaluate[/color]
[color=blue]Dim[/color] strB40 [color=blue]As[/color] [color=blue]String[/color], strC40 [color=blue]As[/color] [color=blue]String[/color], strEvalB40 [color=blue]As[/color] [color=blue]String[/color]
[color=blue]Let[/color] strB40 = Range("B40").Value: [color=blue]Let[/color] strC40 = Range("C40").Value

[color=blue]Let[/color] strEvalB40 = "=" & """" & strB40 & """ & ""-""  & """ & strC40 & """"
[color=blue]Let[/color] strEvalB40 = Replace(strEvalB40, """""", """""""") [color=darkgreen]'Not needed but does no harm[/color]
[color=blue]Let[/color] RstEvalB40 = Evaluate(strEvalB40)
[color=blue]Let[/color] Range("D40").Value = RstEvalB40

[color=blue]Let[/color] strEvalB40 = "=" & Range("B40").Address & "" & "&""-"" & """ & strC40 & """"
[color=blue]Let[/color] strEvalB40 = Replace(strEvalB40, """""", """""""") [color=darkgreen]'Not needed but does no harm[/color]
[color=blue]Let[/color] RstEvalB40 = Evaluate(strEvalB40)
[color=blue]Let[/color] Range("D40").Value = RstEvalB40

Rem 2) [color=darkgreen]'ScooBDoo Dumps in Row 41[/color]
[color=blue]Dim[/color] RstEvalB41 [color=blue]As[/color] [color=blue]String[/color] [color=darkgreen]'Expecting a final String result for concatenating cell values that may have a " or two in them using Evaluate[/color]
[color=blue]Dim[/color] strB41 [color=blue]As[/color] [color=blue]String[/color], strC41 [color=blue]As[/color] [color=blue]String[/color], strEvalB41 [color=blue]As[/color] [color=blue]String[/color]
[color=blue]Let[/color] strB41 = Range("B41").Value: [color=blue]Let[/color] strC41 = Range("C40").Value

[color=blue]Let[/color] strEvalB41 = "=" & """" & strB41 & """ & ""-""  & """ & strC41 & """"
[color=blue]Let[/color] strEvalB41 = Replace(strEvalB41, """""", """""""") [color=darkgreen]'Needed for B and C[/color]
[color=blue]Let[/color] RstEvalB41 = Evaluate(strEvalB41)
[color=blue]Let[/color] Range("D41").Value = RstEvalB41

[color=blue]Let[/color] strEvalB41 = "=" & Range("B41").Address & "" & "&""-"" & """ & strC41 & """"
[color=blue]Let[/color] strEvalB41 = Replace(strEvalB41, """""", """""""") [color=darkgreen]'Needed for C[/color]
[color=blue]Let[/color] RstEvalB41 = Evaluate(strEvalB41)
[color=blue]Let[/color] Range("D41").Value = RstEvalB41

[color=blue]Let[/color] strEvalB41 = "=" & Range("B41").Address & "" & "&""-""&" & "" & Range("C41").Address & ""
[color=blue]Let[/color] strEvalB41 = Replace(strEvalB41, """""", """""""") [color=darkgreen]'Not needed but does no harm[/color]
[color=blue]Let[/color] RstEvalB41 = Evaluate(strEvalB41)
[color=blue]Let[/color] Range("D41").Value = RstEvalB41

Rem 3) [color=darkgreen]'ScooBDoo Dumps over a Range[/color]
[color=blue]Dim[/color] varEvalField() [color=blue]As[/color] [color=blue]Variant[/color] [color=darkgreen]'Expecting to get the Evaluate to chuck us back a Filed of Variant types[/color]
[color=blue]Dim[/color] rngE [color=blue]As[/color] Range, rngB [color=blue]As[/color] Range, rngC [color=blue]As[/color] Range [color=darkgreen]'Column Ranges of all cells of interest[/color]
[color=blue]Set[/color] rngE = Range("E40:E41"): [color=blue]Set[/color] rngB = Range("B40:B41"): [color=blue]Set[/color] rngC = Range("C40:C41"):
[color=blue]Dim[/color] strRngEval [color=blue]As[/color] [color=blue]String[/color]
[color=blue]Let[/color] strRngEval = "=" & rngB.Address & "" & "&""-""&" & "" & rngC.Address & ""
[color=blue]Let[/color] strRngEval = Replace(strRngEval, """""", """""""")
[color=blue]Let[/color] varEvalField() = Evaluate(strRngEval)
[color=blue]Let[/color] rngE.Value = varEvalField() [color=darkgreen]'[/color]
[color=blue]End[/color] [color=blue]Sub[/color]

This is what you get based on that start range I showed at the start:

[Table="width:, class:head"][tr=bgcolor:#888888][th]_Row\^Col[/th][th]

D​

[/th][th]

E​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

40​

[/td][td]SKU-BeDu[/td][td]SKU-BeDu[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

41​

[/td][td]SKU"-BeDu[/td][td]SKU"-BeDu[/td][/tr]
[/table]

 

[RickXL]

Hi,

As they would say on Monty Python's Flying Circus: "And now for something completely different."

I started looking at your code when you posted it. The more I looked the more simplifications I could see.

It looks to me as if you are looking to add a Trend column based on the previous 14 (I may be plus or minus 1 here) Actuals. If you don't have enough actuals for a trend then you just insert the Actual value as it is.

Assuming the data will be sorted by column B, column D and column A, I think the following should work:

First, add a column that tells you when column B or column D change:
=OR(B2<>B1,D2<>D1)*1
I used column H for the new formula.

Second, use that column to control whether the trend or actual value is calculated:
=IF(IFERROR(IF(MAX(H1:H14)=1,1,0),1)=1,E14,TREND(E1:E14,A1:A14,A14))

Please note that the formula needs to be entered into row 14 and then filled in the rest of the column in both directions because the rows prior to 14 result in an invalid reference - it still returns the Actual, though.

If I were going to do it using VBA then I would probably copy the whole table into an array then create another array with the "New Group" data in it.

 

[DocAElstein]

Hi,
_..... "And now for something …..maybe still missing the point!.......

I have had another quick look, but can still not quite understand fully the problem.
But after reading this..

..... For now the work around was to create a "Clean SKU" column in the table set that strips the double quote from the SKU text and match to that row instead of the [SKU] column. .....

... I thought maybe at the appropriate Evaluate you need to do the reverse of what I had suggested previuosly. Going back to my ScooBDoo Demo Code

_.... same Input data

[Table="width:, class:head"][tr=bgcolor:#888888][th]_Row\^Col[/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

40​

[/td][td]SKU[/td][td]BeDu[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

41​

[/td][td]SKU"[/td][td]BeDu[/td][/tr]
[/table]

_.... add these lines to my Demo code:

Rem 4) [color=darkgreen]'ScooBDoo Dumps the Quote out[/color]
[color=blue]Let[/color] strEvalB40 = "=" & """" & strB40 & """ & ""-""  & """ & strC40 & """"
        [color=darkgreen]'Let strEvalB40 = Replace(strEvalB40, """""", """") 'Not needed but does no harm[/color]
[color=blue]Let[/color] strEvalB40 = Replace(strEvalB40, Chr(34) & Chr(34), Chr(34)) [color=darkgreen]'Not needed but does no harm[/color]
[color=blue]Let[/color] RstEvalB40 = Evaluate(strEvalB40)
[color=blue]Let[/color] Range("F40").Value = RstEvalB40

[color=blue]Let[/color] strEvalB41 = "=" & """" & strB41 & """ & ""-""  & """ & strC41 & """"
        [color=darkgreen]'Let strEvalB41 = Replace(strEvalB41, """""", """") 'Needed to take B Out[/color]
[color=blue]Let[/color] strEvalB41 = Replace(strEvalB40, Chr(34) & Chr(34), Chr(34)) [color=darkgreen]'Needed to take B Out[/color]
[color=blue]Let[/color] RstEvalB41 = Evaluate(strEvalB41)
[color=blue]Let[/color] Range("F41").Value = RstEvalB41

_...

Then you get this output.

[Table="width:, class:head"][tr=bgcolor:#888888][th]_Row\^Col[/th][th]

F​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

40​

[/td][td]SKU-BeDu[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

41​

[/td][td]SKU-BeDu[/td][/tr]
[/table]

The important “New Evaluate” would be instead of doing this
= Evaluate(strEval)

You do this
= Evaluate(Replace(strEval, """""", """"))
Or
= Evaluate(Replace(strEval, Chr(34) & Chr(34), Chr(34)))


_.....................................
I expect I am still missing the point. I tried to understand and run your code to maybe see if I could take it further.
I used your data and I think successfully made the List Object Table. ( - ....it then accepts this for example
Set tbl = ActiveSheet.ListObjects("RawSalesDataTbl") .... )

I declared some undeclared variables and attempted to run the code. Unfortunately it always errors at this line:

Set weekRng = ActiveSheet.Range("RawSalesDataTbl[Week '#]")

Commenting that line out the code goes further, accepting, for example these lines


Set SKURng = ActiveSheet.Range("RawSalesDataTbl[SKU]")
Set sourceRng = ActiveSheet.Range("RawSalesDataTbl[Source]")

But of course with that weekRng not set the code will error at further points. I was not able to Google any info on what this
[Week '#]
Rather than the normal
[Week]
Without sorting that out i think I cannot help further.

Alan