• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 246
  • Last Modified:

Script error for "Sub WorkBook_Beforeclose"

Hi Experts,

I'm using this script for "Sub WorkBook_Beforeclose". For some reason the referred line shows as:
Compile error: Expected end of statement.

ResultD3 = "=SUMPRODUCT((LEFT('Detail Task'!$D$7:$D$493,1)={"H","W"})*('Detail Task'!$B$7:$B$493>='Weekly Summary Report'!$B3)*('Detail Task'!$B$7:$B$493<='Weekly Summary Report'!$C3)*('Detail Task'!$H$7:$H$493))"

When debug, this line highlighted in "yellow":  Range("D3") = vResultD3

I've attached the whole script for Experts to evaluate. Need your advice.

Option Explicit
Sub WorkBook_Beforeclose(Cancel As Boolean)
Dim vResultG3, vResultG4, vResultG5, vResultG6, vResultG7 As Variant

Sheets("Weekly Summary Report").Select

vResultG3 = "=SUMPRODUCT((LEFT('Detail Task'!$D$7:$D$503,1)=""C"")*('Detail Task'!$B$7:$B$503>='Weekly Summary Report'!$B3)*('Detail Task'!$B$7:$B$503<='Weekly Summary Report'!$C3)*('Detail Task'!$H$7:$H$503))"
vResultG4 = "=SUMPRODUCT((LEFT('Detail Task'!$D$7:$D$503,1)=""C"")*('Detail Task'!$B$7:$B$503>='Weekly Summary Report'!$B4)*('Detail Task'!$B$7:$B$503<='Weekly Summary Report'!$C4)*('Detail Task'!$H$7:$H$503))"
vResultG5 = "=SUMPRODUCT((LEFT('Detail Task'!$D$7:$D$503,1)=""C"")*('Detail Task'!$B$7:$B$503>='Weekly Summary Report'!$B5)*('Detail Task'!$B$7:$B$503<='Weekly Summary Report'!$C5)*('Detail Task'!$H$7:$H$503))"
vResultG6 = "=SUMPRODUCT((LEFT('Detail Task'!$D$7:$D$503,1)=""C"")*('Detail Task'!$B$7:$B$503>='Weekly Summary Report'!$B6)*('Detail Task'!$B$7:$B$503<='Weekly Summary Report'!$C6)*('Detail Task'!$H$7:$H$503))"
vResultG7 = "=SUMPRODUCT((LEFT('Detail Task'!$D$7:$D$503,1)=""C"")*('Detail Task'!$B$7:$B$503>='Weekly Summary Report'!$B7)*('Detail Task'!$B$7:$B$503<='Weekly Summary Report'!$C7)*('Detail Task'!$H$7:$H$503))"

Range("G3") = vResultG3
Range("G4") = vResultG4
Range("G5") = vResultG5
Range("G6") = vResultG6
Range("G7") = vResultG7


Dim vResultF3, vResultF4, vResultF5, vResultF6, vResultF7 As Variant
Sheets("Weekly Summary Report").Select

vResultF3 = "=SUMPRODUCT((LEFT('Detail Task'!$D$7:$D$503,1)=""L"")*('Detail Task'!$B$7:$B$503>='Weekly Summary Report'!$B3)*('Detail Task'!$B$7:$B$503<='Weekly Summary Report'!$C3)*('Detail Task'!$H$7:$H$503))"
vResultF4 = "=SUMPRODUCT((LEFT('Detail Task'!$D$7:$D$503,1)=""L"")*('Detail Task'!$B$7:$B$503>='Weekly Summary Report'!$B4)*('Detail Task'!$B$7:$B$503<='Weekly Summary Report'!$C4)*('Detail Task'!$H$7:$H$503))"
vResultF5 = "=SUMPRODUCT((LEFT('Detail Task'!$D$7:$D$503,1)=""L"")*('Detail Task'!$B$7:$B$503>='Weekly Summary Report'!$B5)*('Detail Task'!$B$7:$B$503<='Weekly Summary Report'!$C5)*('Detail Task'!$H$7:$H$503))"
vResultF6 = "=SUMPRODUCT((LEFT('Detail Task'!$D$7:$D$503,1)=""L"")*('Detail Task'!$B$7:$B$503>='Weekly Summary Report'!$B6)*('Detail Task'!$B$7:$B$503<='Weekly Summary Report'!$C6)*('Detail Task'!$H$7:$H$503))"
vResultF7 = "=SUMPRODUCT((LEFT('Detail Task'!$D$7:$D$503,1)=""L"")*('Detail Task'!$B$7:$B$503>='Weekly Summary Report'!$B7)*('Detail Task'!$B$7:$B$503<='Weekly Summary Report'!$C7)*('Detail Task'!$H$7:$H$503))"

Range("F3") = vResultF3
Range("F4") = vResultF4
Range("F5") = vResultF5
Range("F6") = vResultF6
Range("F7") = vResultF7


Dim vResultE3, vResultE4, vResultE5, vResultE6, vResultE7 As Variant
Sheets("Weekly Summary Report").Select

vResultE3 = "=SUMPRODUCT((LEFT('Detail Task'!$D$7:$D$503,1)=""S"")*('Detail Task'!$B$7:$B$503>='Weekly Summary Report'!$B3)*('Detail Task'!$B$7:$B$503<='Weekly Summary Report'!$C3)*('Detail Task'!$H$7:$H$503))"
vResultE4 = "=SUMPRODUCT((LEFT('Detail Task'!$D$7:$D$503,1)=""S"")*('Detail Task'!$B$7:$B$503>='Weekly Summary Report'!$B4)*('Detail Task'!$B$7:$B$503<='Weekly Summary Report'!$C4)*('Detail Task'!$H$7:$H$503))"
vResultE5 = "=SUMPRODUCT((LEFT('Detail Task'!$D$7:$D$503,1)=""S"")*('Detail Task'!$B$7:$B$503>='Weekly Summary Report'!$B5)*('Detail Task'!$B$7:$B$503<='Weekly Summary Report'!$C5)*('Detail Task'!$H$7:$H$503))"
vResultE6 = "=SUMPRODUCT((LEFT('Detail Task'!$D$7:$D$503,1)=""S"")*('Detail Task'!$B$7:$B$503>='Weekly Summary Report'!$B6)*('Detail Task'!$B$7:$B$503<='Weekly Summary Report'!$C6)*('Detail Task'!$H$7:$H$503))"
vResultE7 = "=SUMPRODUCT((LEFT('Detail Task'!$D$7:$D$503,1)=""S"")*('Detail Task'!$B$7:$B$503>='Weekly Summary Report'!$B7)*('Detail Task'!$B$7:$B$503<='Weekly Summary Report'!$C7)*('Detail Task'!$H$7:$H$503))"

Range("E3") = vResultE3
Range("E4") = vResultE4
Range("E5") = vResultE5
Range("E6") = vResultE6
Range("E7") = vResultE7



Dim vResultD3, vResultD4, vResultD5, vResultD6, vResultD7 As Variant
Sheets("Weekly Summary Report").Select

vResultD3 = "=SUMPRODUCT((LEFT('Detail Task'!$D$7:$D$493,1)={"H","W"})*('Detail Task'!$B$7:$B$493>='Weekly Summary Report'!$B3)*('Detail Task'!$B$7:$B$493<='Weekly Summary Report'!$C3)*('Detail Task'!$H$7:$H$493))"
vResultD4 = "=SUMPRODUCT((LEFT('Detail Task'!$D$7:$D$493,1)={"H","W"})*('Detail Task'!$B$7:$B$493>='Weekly Summary Report'!$B3)*('Detail Task'!$B$7:$B$493<='Weekly Summary Report'!$C4)*('Detail Task'!$H$7:$H$493))"
vResultD5 = "=SUMPRODUCT((LEFT('Detail Task'!$D$7:$D$493,1)={"H","W"})*('Detail Task'!$B$7:$B$493>='Weekly Summary Report'!$B3)*('Detail Task'!$B$7:$B$493<='Weekly Summary Report'!$C5)*('Detail Task'!$H$7:$H$493))"
vResultD6 = "=SUMPRODUCT((LEFT('Detail Task'!$D$7:$D$493,1)={"H","W"})*('Detail Task'!$B$7:$B$493>='Weekly Summary Report'!$B3)*('Detail Task'!$B$7:$B$493<='Weekly Summary Report'!$C6)*('Detail Task'!$H$7:$H$493))"
vResultD7 = "=SUMPRODUCT((LEFT('Detail Task'!$D$7:$D$493,1)={"H","W"})*('Detail Task'!$B$7:$B$493>='Weekly Summary Report'!$B3)*('Detail Task'!$B$7:$B$493<='Weekly Summary Report'!$C7)*('Detail Task'!$H$7:$H$493))"

Range("D3") = vResultD3
Range("D4") = vResultD4
Range("D5") = vResultD5
Range("D6") = vResultD6
Range("D7") = vResultD7
End Sub

Open in new window

0
Theva
Asked:
Theva
1 Solution
 
Chris BottomleyCommented:
You overlooked a doubling up of the quotes:

Chris
vResultD3 = "=SUMPRODUCT((LEFT('Detail Task'!$D$7:$D$493,1)={""H"",""W""})*('Detail Task'!$B$7:$B$493>='Weekly Summary Report'!$B3)*('Detail Task'!$B$7:$B$493<='Weekly Summary Report'!$C3)*('Detail Task'!$H$7:$H$493))" 
vResultD4 = "=SUMPRODUCT((LEFT('Detail Task'!$D$7:$D$493,1)={""H"",""W""})*('Detail Task'!$B$7:$B$493>='Weekly Summary Report'!$B3)*('Detail Task'!$B$7:$B$493<='Weekly Summary Report'!$C4)*('Detail Task'!$H$7:$H$493))" 
vResultD5 = "=SUMPRODUCT((LEFT('Detail Task'!$D$7:$D$493,1)={""H"",""W""})*('Detail Task'!$B$7:$B$493>='Weekly Summary Report'!$B3)*('Detail Task'!$B$7:$B$493<='Weekly Summary Report'!$C5)*('Detail Task'!$H$7:$H$493))" 
vResultD6 = "=SUMPRODUCT((LEFT('Detail Task'!$D$7:$D$493,1)={""H"",""W""})*('Detail Task'!$B$7:$B$493>='Weekly Summary Report'!$B3)*('Detail Task'!$B$7:$B$493<='Weekly Summary Report'!$C6)*('Detail Task'!$H$7:$H$493))" 
vResultD7 = "=SUMPRODUCT((LEFT('Detail Task'!$D$7:$D$493,1)={""H"",""W""})*('Detail Task'!$B$7:$B$493>='Weekly Summary Report'!$B3)*('Detail Task'!$B$7:$B$493<='Weekly Summary Report'!$C7)*('Detail Task'!$H$7:$H$493))" 

Open in new window

0
 
ThevaAuthor Commented:
Thanks Chris.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now