Excel 2003 + help please with button
 

Hi all, can anyone tell me why i'm getting a Compile Error: Syntax Error on
the line:

For lng = 0 me.ListBox1.listcount - 1?

Thanks.

Sub Button2_Click()

Dim lng As Long
Dim col As Collection
Dim wb2 As Workbook
Dim ws As Worksheet

Set col = New Collection
For lng = 0 Me.Neil.ListCount - 1
If Me.ListBox1.Selected(lng) Then
col.Add Me.ListBox1.List(lng)
End If
Next lng

If col.Count = 0 Then
MsgBox "No Selections"
Else
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set wb1 = ActiveWorkbook
Set wb2 = Workbooks.Open("\\sguk-app1\Business Objects\CHR\Export of
SGUK.xls ")
Set ws = wb2.Sheets("Sheet1")

For Each wks In col
wks.Range("A8:T27").Copy ws.Cells(Rows.Count,
"A").End(xlUp).Offset(1, 0)
Next wks
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End If


End Sub

Excel 2003 + help please with button
 

Your For statement has no "To". Try:
For lng = 0 To Me.Neil.ListCount - 1

Regards,
Fred

"Neil Holden" wrote in message
...
Hi all, can anyone tell me why i'm getting a Compile Error: Syntax Error
on
the line:

For lng = 0 me.ListBox1.listcount - 1?

Thanks.

Sub Button2_Click()

Dim lng As Long
Dim col As Collection
Dim wb2 As Workbook
Dim ws As Worksheet

Set col = New Collection
For lng = 0 Me.Neil.ListCount - 1
If Me.ListBox1.Selected(lng) Then
col.Add Me.ListBox1.List(lng)
End If
Next lng

If col.Count = 0 Then
MsgBox "No Selections"
Else
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set wb1 = ActiveWorkbook
Set wb2 = Workbooks.Open("\\sguk-app1\Business Objects\CHR\Export
of
SGUK.xls ")
Set ws = wb2.Sheets("Sheet1")

For Each wks In col
wks.Range("A8:T27").Copy ws.Cells(Rows.Count,
"A").End(xlUp).Offset(1, 0)
Next wks
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End If


End Sub

Excel 2003 + help please with button
 

You need "to"

For lng = 0 to Me.Neil.ListCount - 1

--
Gary''s Student - gsnu201001

Excel 2003 + help please with button
 

I've tried that and still getting errors, thanks for your help though.

Neil.

"Fred Smith" wrote:

Your For statement has no "To". Try:
For lng = 0 To Me.Neil.ListCount - 1

Regards,
Fred

"Neil Holden" wrote in message
...
Hi all, can anyone tell me why i'm getting a Compile Error: Syntax Error
on
the line:

For lng = 0 me.ListBox1.listcount - 1?

Thanks.

Sub Button2_Click()

Dim lng As Long
Dim col As Collection
Dim wb2 As Workbook
Dim ws As Worksheet

Set col = New Collection
For lng = 0 Me.Neil.ListCount - 1
If Me.ListBox1.Selected(lng) Then
col.Add Me.ListBox1.List(lng)
End If
Next lng

If col.Count = 0 Then
MsgBox "No Selections"
Else
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set wb1 = ActiveWorkbook
Set wb2 = Workbooks.Open("\\sguk-app1\Business Objects\CHR\Export
of
SGUK.xls ")
Set ws = wb2.Sheets("Sheet1")

For Each wks In col
wks.Range("A8:T27").Copy ws.Cells(Rows.Count,
"A").End(xlUp).Offset(1, 0)
Next wks
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End If


End Sub


.

Excel 2003 + help please with button
 

It worked for me when I put it into the VBA Editor. Did you copy and paste
it?

Regards,
Fred

"Neil Holden" wrote in message
...
I've tried that and still getting errors, thanks for your help though.

Neil.

"Fred Smith" wrote:

Your For statement has no "To". Try:
For lng = 0 To Me.Neil.ListCount - 1

Regards,
Fred

"Neil Holden" wrote in message
...
Hi all, can anyone tell me why i'm getting a Compile Error: Syntax
Error
on
the line:

For lng = 0 me.ListBox1.listcount - 1?

Thanks.

Sub Button2_Click()

Dim lng As Long
Dim col As Collection
Dim wb2 As Workbook
Dim ws As Worksheet

Set col = New Collection
For lng = 0 Me.Neil.ListCount - 1
If Me.ListBox1.Selected(lng) Then
col.Add Me.ListBox1.List(lng)
End If
Next lng

If col.Count = 0 Then
MsgBox "No Selections"
Else
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set wb1 = ActiveWorkbook
Set wb2 = Workbooks.Open("\\sguk-app1\Business
Objects\CHR\Export
of
SGUK.xls ")
Set ws = wb2.Sheets("Sheet1")

For Each wks In col
wks.Range("A8:T27").Copy ws.Cells(Rows.Count,
"A").End(xlUp).Offset(1, 0)
Next wks
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End If


End Sub


.