Compile error expected list separator or

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.

   ---

Guys,

Thanks again, the DLookup is now working, however I am rendering a
«type mismatch» error

Here is the code:
If Me.Login_PW = DLookup(«Staff_Password», «Security_Staff»,
«[Staff_Name]= ‘» & Me.LogIn_ID & «‘») Then
Staff_Name = Me.LogIn_ID

The debugger is pointing to «Staff_Name = Me.LogIn_ID»

Me.LogIn_ID is a string

-M

Damian S wrote:
Hi Mr.Kane,

Try opening the immediate window (Ctrl-G) and typing
? DLookup(«Staff_Password», «Security_Staff», «[Staff_ID]= ‘» & Me.LogIn_ID
& «‘»)

(including the question mark), and that will let you know if the dlookup
portion is working correctly. You may need to replace the me.LogIn_ID with
forms!frmName.LogIn_ID, and ensure that you have the form open.

Damian.

:

Alright guys,

Apparently I have been working way too long on this and just overlooked

the «source» table.

I now have the correct table but am coming across the «Expected list
separator or )» error again.

Here is the current code:
If Me.Login_PW = DLookup(«Staff_Password», «Security_Staff»,
«[Staff_ID]= ‘» &Me.LogIn_ID& «‘»)

This code segment is highlighted by the debugger: &Me.LogIn_ID& «‘»)
(the double quotes, single quote, double quotes portion)

I am baffled.
-M

Mr.Kane wrote:
Damian and David,

Thank you for taking the time out to offer a solution. David’s Syntax
suggestion
DLookup(«Staff_Password», «Staff», «[Staff_ID]= Me.LogIn_ID»)

seems to have done the trick, however now I am encoutering the
following error:
Run-Time error ‘2471’
‘the object doesn’t contain the Automation object ‘Staff_Password.»

Any help would be appreciated
-M

David F Cox wrote:
It is perhaps the wrong hour of the morning to be trying to use my brain,
but:
DLookup(«Staff_Password», «Staff», «[Staff_ID]= Me.LogIn_ID»)

Hi Mr.Kane,

If login_ID is a number, you do not need the extra quotes at all… If it
is a string, try using a single quote like this:

DLookup(«Staff_Password», «Staff», «[Staff_ID]='» & Me.LogIn_ID & «‘»)

You might also want to consider checking for null in case you have no
matching Staff in the table with the entered Staff_ID. Also, just to
prevent
confusion, if you are calling it Staff_ID in the table, why not call it
Staff_ID on your form also for consistency.

Hope this helps.

Damian.

where after the equals sign is a single quote then double quote, then
after
the ampersand is a double quote, single quote, double quote.

:

I am creating a login form and keep running into the same compile
error: «Expected: List separator or )»

Here is the offending code segment:
If Me.Login_PW = DLookup(«Staff_Password», «Staff», «[Staff_ID]='»
&Me.LogIn_ID& «»‘)Then
Staff_Name = Me.LogIn_ID.Value

The highlighted segment of code are the 2 double quotes after
&Me.LogIn_ID&

I’m sure the correct syntax will be obvious after the fact but it’s
incredibly frustrating right now.

any help would be appreciated
I am willing to send anyone who can help a screenshot of the error for
your evaluation. I believe
that I have the order correct but it may help you to see the error and
the highlighted code segment.

Thank you,
Marc