List Seperator error

NZAS

Board Regular
Joined
Oct 18, 2012
Messages
104
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I have and error when writing some VBA. I have had to use formatting to get code to work but get the error list seperator erorr.
the code eroe ocus when I add the parameter fo the search range using a between dtaes for nmedd range on peadsheet,
the code is



Code:
[FONT=Calibri][COLOR=#000000]SubMacro1()[/COLOR][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][COLOR=#000000]'[/COLOR][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][COLOR=#000000]' Macro1Macro[/COLOR][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][COLOR=#000000]'[/COLOR][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][COLOR=#000000]Dim sDate, eDate[/COLOR][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][COLOR=#000000]sDate =DateValue(.Range("sDate"))[/COLOR][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000][FONT=Calibri][COLOR=#000000]eDate =DateValue(.Range("eDate"))[/COLOR][/FONT][/COLOR][/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=#000000][FONT=Calibri][COLOR=#000000]
[/COLOR][/FONT][/COLOR][/SIZE][/FONT]


[FONT=Calibri][COLOR=#000000]WithSheets("Data")[/COLOR][/FONT][FONT=Calibri][COLOR=#000000][FONT=Times New Roman][SIZE=3][COLOR=#000000][FONT=Calibri][COLOR=#000000]sDate = Format(sDate, "yyyy-mm-ddhh:mm:ss")
       [FONT=Calibri][COLOR=#000000]eDate = Format(eDate, "yyyy-mm-ddhh:mm:ss")[/COLOR][/FONT][/COLOR][/FONT]
[/COLOR][/SIZE][/FONT][/COLOR][/FONT]


[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][FONT=Calibri][COLOR=#000000]Range("A5").Select[/COLOR][/FONT]
[/COLOR][/SIZE][/FONT]

[FONT=Calibri][COLOR=#000000]With Selection.ListObject.QueryTable[/COLOR][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000][FONT=Calibri][COLOR=#000000].Connection = Array(Array( _[/COLOR][/FONT][/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000][FONT=Calibri][COLOR=#000000]"ODBC:[/COLOR][COLOR=#000000]PRD;UID=metpro_ro_user;PWD=metpro_ro_user;DBQ=NMPP.WORLD;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfA"_[/COLOR][/FONT]
[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][FONT=Calibri][COLOR=#000000]),Array( _[/COLOR][/FONT][/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000][FONT=Calibri][COLOR=#000000]"llSuccessful;NUM=MS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;MLD=0;ODA=F;"_[/COLOR][/FONT][/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000][FONT=Calibri][COLOR=#000000]))[/COLOR][/FONT][/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000][FONT=Calibri][COLOR=#000000].CommandText = Array( _[/COLOR][/FONT][/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000][FONT=Calibri][COLOR=#000000]"SELECT VW_LOTS.DATETIME_WEIGHED, VW_LOTS.POUR_MACHINE,VW_LOTS.POUR, VW_LOTS.LOT, VW_LOTS.ALLOY, VW_LOTS.PRODUCT, VW_LOTS.WEIGHT"& Chr(13) & "" & Chr(10) & "FROM METPRO.VW_LOTSVW_LOTS" & Chr(13) & "" & Chr(10)[/COLOR][COLOR=#000000] &"WHERE (VW_LOTS.DATETIME_WEIGHED Between {" _[/COLOR][/FONT][/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000][FONT=Calibri][COLOR=#000000], ts '" & sDate "} And{ts " & eDate "'})")[/COLOR][/FONT][/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000][FONT=Calibri][COLOR=#000000].Refresh BackgroundQuery:=False[/COLOR][/FONT][/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000][FONT=Calibri][COLOR=#000000]End With[/COLOR][/FONT][/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000][FONT=Calibri][COLOR=#000000]Range("A5").Select[/COLOR][/FONT][/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000][FONT=Calibri][COLOR=#000000]Sheets("Pivot").Select[/COLOR][/FONT]
[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][FONT=Calibri][COLOR=#000000]ActiveSheet.PivotTables("PivotTable2").PivotSelect"'2J813'", xlDataAndLabel + _[/COLOR][/FONT][/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000][FONT=Calibri][COLOR=#000000]xlFirstRow, True[/COLOR][/FONT][/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000][FONT=Calibri][COLOR=#000000]Range("B6").Select[/COLOR][/FONT][/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000][FONT=Calibri][COLOR=#000000]ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh[/COLOR][/FONT][/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000][FONT=Calibri][COLOR=#000000]Sheets("Data").Select[/COLOR][/FONT][/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000][FONT=Calibri][COLOR=#000000]Range("D1").Select[/COLOR][/FONT][/COLOR][/SIZE][/FONT]

[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][COLOR=#000000]End Sub[/COLOR][/FONT]
Cheers NZAS
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Watch MrExcel Video

Forum statistics

Threads
1,109,001
Messages
5,526,203
Members
409,686
Latest member
Tori83

This Week's Hot Topics

Top