Type Mismatch Error

[chaddug]

Please help me, I have been working on trying to get this sub to change the criteria in my query for about 2 days now and I just cannot figure it out. I think the problem is in the fact that Visual Basic is splitting the code into a bunch of lines, but honestly, I have no idea. I have never had this problem before. Here is my code:

Please Login or Register  to view this content.

[fredlo2010]

Hello,

See if this helps

Please Login or Register  to view this content.

[chaddug]

Thanks for the reply! Unfortunately that did not solve the problem. I had to change your code a little because my sheets are not called 'Sheet1' 'Sheet2' ... etc. Is there anything else you changed in the code other than using Sheets("Sheet Name"). instead of Sheet1.?

Please Login or Register  to view this content.

I can't upload the workbook because it is massive, but I took some screen shots of what I am getting.

Debug.JPG
Sheet Names.JPG
Type Mismatch.JPG

Here is a table giving an idea of what the sheet that the macro is pulling its values from looks like and the formulas in those cells.

Formulas
Column D =IF(ISBLANK(INDIRECT("A"&ROW())),"",IF(ISBLANK(INDIRECT("A"&ROW()+1)),"'"&
INDIRECT("A"&ROW())&"'","'"&INDIRECT("A"&ROW())&"'"&IF(OR(ROW()=41,ROW()=81,ROW()=121,ROW()=161,
ROW()=201),"",",")))

Column H =IF(csvRange(D2:D41)="","'0'",csvRange(D2:D41))

	A	B	C	D	E	F	G	H	I
1	SKU	Name		Formatted SKU				Include Since	2005
2	54321	'Book Name1'		'54321',
3	98765	'Book Name2'		'98765',			Line	Criteria
4	76543	'Book Name3'		'76543',			Line 1	'54321','98765','76543',...

Also, here is the code for the csvRange

Please Login or Register  to view this content.

[Norie]

Have you checke the values in line1, line2... etc?

Also, have you checked the query with hard-coded values?

[chaddug]

Have you checke the values in line1, line2... etc?

Also, have you checked the query with hard-coded values?

Im not exactly sure I understand your question, but when looking at the sheet with the line1, line2, etc values it displays then as a string of skus (so I think that is correct). And the query is working fine, I just want the people who are using this sheet to be able to just click refresh and have the macro update the query with new criteria.

[Norie]

I mean have you checked the values of the variables line1, line2... etc when the code runs?

You can do that in the Locals Window (View>Locals Window).

[chaddug]

This is what I get

Attachment 264882