Excel 07 vs. 03 "listrows" reference <subscript out of range
 

I developed an App in xl-07, works big. Tried to verify in xl-03, "NOT"! Made
many changes to correct errors. One isn't copperating:

Sub CustSaveRow() 'Located in Module 1
Dim wrksht As Worksheet
Set wrksht = Sheet8 'This sheet has Listobject(1) Cust info
Dim Cust As String
Cust = frmHome.cmbCustomer.Text 'combobox with selected Cust info to change
Dim Ct As Integer 'ListObject includes an "Index" field to determine the row #
Ct = WorksheetFunction.VLookup(Cust, wrksht.ListObjects(1).Range, 7, False)
Dim Saverow As ListRow
Set Saverow = Sheet8.ListObjects(1).ListRows(Ct) <subscipt out of range
error!
With Saverow
.Range(1) = frmNewCust.tbCustomer.Text
.Range(2) = frmNewCust.tbAcctNum.Text
.Range(3) = frmNewCust.tbCity.Text
.Range(4) = frmNewCust.tbState.Text
.Range(5) = frmNewCust.tbContact.Text
.Range(6) = frmNewCust.tbPhone.Text
.Range(8) = Saverow.Range(1) 'This repeats Range(1) for other
vlookups
End With
MsgBox (wrksht.ListObjects(1).ListRows(Ct).Range(1) & " has been updated")
End Sub

This works well and good in Excel 2007, but does not in Excel 2003. I have a
similar procedure for "deleting" a customer row, which currently also doesn't
work in '03 for the same reason.

I'm tempted to try to convert lists to ranges. Thoughts?

Excerpt from one post regarding error:

Subject: Subscript out of range problem 9/15/2004 9:59 AM PST

By: In:
microsoft.public.excel.programming


hi,
you get that error when you tell xl to do something it
cann't do.

Excel 07 vs. 03 "listrows" reference <subscript out of range
 

i don't believe listobjects are available in v03.

"CBartman" wrote:

I developed an App in xl-07, works big. Tried to verify in xl-03, "NOT"! Made
many changes to correct errors. One isn't copperating:

Sub CustSaveRow() 'Located in Module 1
Dim wrksht As Worksheet
Set wrksht = Sheet8 'This sheet has Listobject(1) Cust info
Dim Cust As String
Cust = frmHome.cmbCustomer.Text 'combobox with selected Cust info to change
Dim Ct As Integer 'ListObject includes an "Index" field to determine the row #
Ct = WorksheetFunction.VLookup(Cust, wrksht.ListObjects(1).Range, 7, False)
Dim Saverow As ListRow
Set Saverow = Sheet8.ListObjects(1).ListRows(Ct) <subscipt out of range
error!
With Saverow
.Range(1) = frmNewCust.tbCustomer.Text
.Range(2) = frmNewCust.tbAcctNum.Text
.Range(3) = frmNewCust.tbCity.Text
.Range(4) = frmNewCust.tbState.Text
.Range(5) = frmNewCust.tbContact.Text
.Range(6) = frmNewCust.tbPhone.Text
.Range(8) = Saverow.Range(1) 'This repeats Range(1) for other
vlookups
End With
MsgBox (wrksht.ListObjects(1).ListRows(Ct).Range(1) & " has been updated")
End Sub

This works well and good in Excel 2007, but does not in Excel 2003. I have a
similar procedure for "deleting" a customer row, which currently also doesn't
work in '03 for the same reason.

I'm tempted to try to convert lists to ranges. Thoughts?

Excerpt from one post regarding error:

Subject: Subscript out of range problem 9/15/2004 9:59 AM PST

By: In:
microsoft.public.excel.programming


hi,
you get that error when you tell xl to do something it
cann't do.

Excel 07 vs. 03 "listrows" reference <subscript out of range
 

It looks like you are assingning Ct based on a lookup in the entire
ListObject.Range, but then using it based on the ListObject.ListRows. I
think if you're Vlookup found the last row in a List that totalled 10 rows,
inlcuding headers, and you then tried to access ListRows(10) it would truly
be "out of range". Here's a simple macro I ran on a 10 row list to show
what I'm saying:

Sub test()
Dim Ct As Long
With Worksheets(1).ListObjects(1)
Ct = .Range.Rows(.Range.Rows.Count).Row
Debug.Print .Range.Rows(Ct).Address
Debug.Print .ListRows(Ct - 1).Range.Address
End With
End Sub

Immediate Window:
$A$10:$B$10
$A$10:$B$10

"CBartman" wrote in message
...
I developed an App in xl-07, works big. Tried to verify in xl-03, "NOT"!
Made
many changes to correct errors. One isn't copperating:

Sub CustSaveRow() 'Located in Module 1
Dim wrksht As Worksheet
Set wrksht = Sheet8 'This sheet has Listobject(1) Cust info
Dim Cust As String
Cust = frmHome.cmbCustomer.Text 'combobox with selected Cust info to
change
Dim Ct As Integer 'ListObject includes an "Index" field to determine the
row #
Ct = WorksheetFunction.VLookup(Cust, wrksht.ListObjects(1).Range, 7,
False)
Dim Saverow As ListRow
Set Saverow = Sheet8.ListObjects(1).ListRows(Ct) <subscipt out of range
error!
With Saverow
.Range(1) = frmNewCust.tbCustomer.Text
.Range(2) = frmNewCust.tbAcctNum.Text
.Range(3) = frmNewCust.tbCity.Text
.Range(4) = frmNewCust.tbState.Text
.Range(5) = frmNewCust.tbContact.Text
.Range(6) = frmNewCust.tbPhone.Text
.Range(8) = Saverow.Range(1) 'This repeats Range(1) for other
vlookups
End With
MsgBox (wrksht.ListObjects(1).ListRows(Ct).Range(1) & " has been updated")
End Sub

This works well and good in Excel 2007, but does not in Excel 2003. I have
a
similar procedure for "deleting" a customer row, which currently also
doesn't
work in '03 for the same reason.

I'm tempted to try to convert lists to ranges. Thoughts?

Excerpt from one post regarding error:

Subject: Subscript out of range problem 9/15/2004 9:59 AM PST

By: In:
microsoft.public.excel.programming


hi,
you get that error when you tell xl to do something it
cann't do.