Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 + help please with button
You need "to"
For lng = 0 to Me.Neil.ListCount - 1 -- Gary''s Student - gsnu201001 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 . |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I add a password button to 2003 Excel? | Excel Discussion (Misc queries) | |||
Excel 2003 - clear button | Excel Discussion (Misc queries) | |||
Themes button in Excel 2003 | Excel Discussion (Misc queries) | |||
Excel 2003 REDO Button | Excel Worksheet Functions | |||
Fx button in Excel 2003 | Excel Worksheet Functions |