List separator or ошибка vba

I am trying to create an Excel VBA button code which will copy a data in columns A8:A399, B8:B399, C8:C399, D8:D399, E8:E399, F8:F399, G8:G399, H8:H399 one of the excel workbook to another workbook without opening it. But the Code I am using gives me an error message saying:

Compile Error, Expected: List Separator or )

and it highlights the (:) in Set myData = Workbooks.SaveAs(“C:UsersathifDesktopTest_PosDataBase.xlsx”). Please tell me a solution.

Here is my Code:

Private Sub CommandButton1_Click()
    LR = Range("A399").End(xlUp).Row
    LR1 = Range("B399").End(xlUp).Row
    LR2 = Range("C399").End(xlUp).Row
    LR3 = Range("D399").End(xlUp).Row
    LR4 = Range("E399").End(xlUp).Row
    LR5 = Range("F399").End(xlUp).Row
    LR6 = Range("G399").End(xlUp).Row
    LR7 = Range("H399").End(xlUp).Row

    Dim itemIndex As String
    Dim itemNumber As String
    Dim itemDetails As String
    Dim itemPrice As Single
    Dim itemCust_nam As String
    Dim itemMobile As String
    Dim itemDate As String
    Dim itemTime As String
    Dim myData As Workbook

Worksheets(“Sheet1”).Select
    itemIndex = Range("A8:A" & LR)
    itemNumber = Range("B8:B" & LR1)
    itemDetails = Range("C8:C" & LR2)
    itemPrice = Range("D8:D" & LR3)
    itemCust_nam = Range("E8:E" & LR4)
    itemMobile = Range("F8:F" & LR5)
    itemDate = Range("G8:G" & LR6)
    itemTime = Range("H8:H" & LR7)


Set myData = Workbooks.SaveAs(“C:UsersathifDesktopTest_PosDataBase.xlsx”)

Worksheets(“Sales”).Select
Worksheets(“Sales”).Range(“A1”).Select
RowCount = Worksheets(“Sales”).Range(“A1”).CurrentRegion.Rows.Count
With Worksheets(“Sales”).Range(“A1”)
    .Offset(RowCount, 0) = itemIndex
    .Offset(RowCount, 1) = itemNumber
    .Offset(RowCount, 2) = itemDetails
    .Offset(RowCount, 3) = itemPrice
    .Offset(RowCount, 4) = itemCust_nam
    .Offset(RowCount, 5) = itemMobile
    .Offset(RowCount, 6) = ItemData
    .Offset(RowCount, 7) = itemTime
End With

myData.Save
End Sub

1. 04-03-2017, 05:01 PM

   
   

   #1

   

   
   Registered User
   

   

   ---

   MsgBox compile syntax error or Expected: list separator or )
   

   I have a simple, I thought, piece of code to check whether recently pasted cells have red font or not.

   It was working well until I tried to add an error check to alert me that no cells were red.

   Here is the code. The variable NoRed is properly defined up at the top.
   After I paste in my text I move things around then check to see if any of 5 cells have red font.

   The MsgBox line keeps giving me either a syntax error (in its current state) or Expected: List separator or ) (if I try to add a title or anything to the msgbox)

   If ActiveCell.Offset(-10, 0).Font.Color = vbRed Then
   ActiveCell.Value = «A»
   GoTo STOPCOUNT
   ElseIf ActiveCell.Offset(-9, 0).Font.Color = vbRed Then
   ActiveCell.Value = «B»
   GoTo STOPCOUNT
   ElseIf ActiveCell.Offset(-8, 0).Font.Color = vbRed Then
   ActiveCell.Value = «C»
   GoTo STOPCOUNT
   ElseIf ActiveCell.Offset(-7, 0).Font.Color = vbRed Then
   ActiveCell.Value = «D»
   GoTo STOPCOUNT
   ElseIf ActiveCell.Offset(-6, 0).Font.Color = vbRed Then
   ActiveCell.Value = «E»
   GoTo STOPCOUNT

   Elseif NoRed = MsgBox (�No Red cells would you like to continue�?�,vbQuestion + vbYesNo)= vbNo Then
   Exit Sub
   End If

   ---

2. 04-03-2017, 07:43 PM

   
   

   #2

   

   
   Registered User
   

   

   ---

   Re: MsgBox compile syntax error or Expected: list separator or )
   

   please use code tags, that’s hard to read

   your ElseIf NoRed statement makes no sense. how did you define NoRed?

   ---

3. 04-03-2017, 09:49 PM

   
   

   #3

   

   
   Registered User
   

   

   ---

   Re: MsgBox compile syntax error or Expected: list separator or )
   

   Sorry, I am new to this, I don’t even know what you mean by code tags.

   The NoRed was defined earlier in the code (Dim NoRed as Integer).
   This is the first mention of NoRed or msgbox in the code. The previous stuff, which I did not show, is just fancy cut and past to move the pasted text into the right cells. I then test it for red font. That is where I am having my difficulties. Any help would be appreciated.

   I should add, I tried it without the NoRed and get the same error

   Elseif MsgBox (“No Red cells would you like to continue…?”,vbQuestion + vbYesNo,)= vbNo Then

   Drummo2a

   Last edited by drummo2a; 04-03-2017 at 09:53 PM.

   ---

4. 04-04-2017, 02:12 AM

   
   

   #4

   Re: MsgBox compile syntax error or Expected: list separator or )
   

   Hi,

   You have smart quotes in that code- you need regular ones- and remove the trailing comma

   Don
   Please remember to mark your thread ‘Solved’ when appropriate.

   ---

5. 04-05-2017, 02:11 PM

   
   

   #5

   

   
   Registered User
   

   

   ---

   Re: MsgBox compile syntax error or Expected: list separator or )
   

   Thanks, I pasted your line of code in and it worked great. Now I have a more questions. Where do the «smart quotes» come from? How can I tell I have them?

   ---

6. 04-07-2017, 01:24 AM

   
   

   #6

   Re: MsgBox compile syntax error or Expected: list separator or )
   

   They sometimes appear if you copy code from a web site or if your code is pasted from Word.

   ---