error subscript out of range in commandbutton_click

[pswanie]

i am calling the following procedure in a command button_click event. its
however giving me a error. subscript out of range...

any sugestions?



textbox20.Value = Sheets("Sheet17").Range("B9").Value & Sheets
("Sheet17").Cells(Sheets("Sheet17").Range("K65535").End(xlUp).Row,
"K").Value
textbox21.Value = Sheets("Sheet17").Range("A9").Value & Sheets
("Sheet17").Cells(Sheets("Sheet17").Range("J65535").End(xlUp).Row,
"J").Value
textbox22.Value = Sheets("Sheet17").Range("C9").Value & Sheets
("Sheet17").Cells(Sheets("Sheet17").Range("L65535").End(xlUp).Row,
"L").Value
textbox24.Value = Sheets("Sheet17").Range("A15").Value & Sheets
("Sheet17").Cells(Sheets("Sheet17").Range("M65535").End(xlUp).Row,
"M").Value
Sheets("Sheet17").Cells(15, "C").Value = (Date) & " " & (Time)
Sheets("Sheet17").Cells(Sheets("Sheet17").Range("I65535").End
(xlUp).Row, "I").Value = (Date) & " " & (Time)

' Check for the data

If textbox20.Value = "" Or textbox21.Value = "" Or textbox22.Value =
"" Or textbox24.Value = "" Then

MsgBox "Some data missing"

End If

 

[Chip Pearson]

Typically, a "subscript out of range" error is thrown when you attempt
to access an element by name from a collection in which no such named
element occurs. The first thing I would check is wheterh you do in
fact have worksheet named "Sheet17". Also, try qualifying the
references to the worksheets with the Workbook. So, instead of

textbox20.Value = Sheets("Sheet17").Range("B9").Value & Sheets....
'use
textbox20.Value = ActiveWorkbook.Sheets("Sheet17").Range("B9").Value &
Sheets

Here, ActiveWorkbook references the workbook that happens to be active
in Excel, which may not be the workbook containing the code. To
reference the workbook containin the code, regardless of what workbook
happens to be active, change ActiveWorkbook to ThisWorkbook.

Also,

Sheets("Sheet17").Cells(Sheets("Sheet17").Range("I65535").End _
(xlUp).Row, "I").Value = (Date) & " " & (Time)

should probably be changed toi

ThisWorkbook.Sheets("Sheet17").Cells(Sheets("Sheet17").Range("I65535").End
_
(xlUp).Row, "I").Value =Format(Now,"dd-mm-yyyy hh:mm")

Change the format string within the quotes ("dd-mm-yyyy hh:mm") to the
date and time format you desire.

Finally,

Sheets("Sheet17").Cells(Sheets("Sheet17").Range("I65535").End

won't work. Change it to
Sheets("Sheet17").Cells(Sheets("Sheet17").Range("AI65535").End
' or, better
With ThisWorkbook.Worksheets("Sheet17")
.Cells(.Rows.Coount,"A").End(xlUp).Value = _
Format(Now,"dd-mmm-yyyy hh:mm")
End With
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)