Solved: Userfrom - Search/Update Code help (excel 97)

[phendrena]

Hi there,

I was wondering if someone could have a look at the following and give me some pointers as to where it's going wrong.
This isn't code that i have written, i came across it while searching and as such i'm adapting it for my needs.
I'm the first to admit my vba knowledge isn't great, but i'm learning (slowly).

Error : Select Method of Range class failed

Form : frmSearch
Worksheet : Sheet1

I've attached the worksheet for you to view.

Here is the code in full, the code fails during the Update Routine :-

Option Explicit
Dim MyArray(500, 25)
Public MyData As Range, c As Range
Dim rFound As Range
Dim r As Long
Dim rng As Range
'------------------------------------------'
'------ Initialize Routine ----'
Private Sub UserForm_Initialize()
Set MyData = Sheet1.Range("A1").CurrentRegion
Me.txtCanxBy.Value = Application.UserName
End Sub
'------------------------------------------'
'------ Search ----'
Sub cmdSearch_Click()
Dim strFind As String
Dim rFilter As Range
Set rFilter = Range(Sheet1.Range("A2"), Sheet1.Range("V65536").End(xlUp))

Set rng = Range(Sheet1.Range("B2"), Sheet1.Range("B65536").End(xlUp))

strFind = Me.txtActionedSearch.Value
With Sheet1

If Not .AutoFilterMode Then .Range("B2").AutoFilter
rFilter.AutoFilter FIELD:=2, Criteria1:=strFind
Set rng = rng.Cells.SpecialCells(xlCellTypeVisible)
Me.lbxResults.Clear
For Each c In rng
With Me.lbxResults
.AddItem c.Value
.List(.ListCount - 1, 1) = c.Offset(0, 2).Value 'Name
.List(.ListCount - 1, 2) = c.Offset(0, 3).Value 'Pol no
.List(.ListCount - 1, 3) = c.Offset(0, 6).Value 'P/Code
.List(.ListCount - 1, 4) = c.Offset(0, 1).Value 'Canx Date
.List(.ListCount - 1, 5) = c.Offset(0, 13).Value 'Claims
.List(.ListCount - 1, 6) = c.Offset(0, 14).Value 'Canx Reason
.List(.ListCount - 1, 7) = c.Offset(0, 19).Value 'Canx By
.List(.ListCount - 1, 8) = c.Offset(0, 20).Value 'Comments
End With
Next c
End With
End Sub
'------------------------------------------'
'------ Select Record ----'
Private Sub lbxResults_Click()
If Me.lbxResults.ListIndex = -1 Then
MsgBox " No selection made"
ElseIf Me.lbxResults.ListIndex >= 0 Then
r = Me.lbxResults.ListIndex
With Me
.txtCustomerName.Value = lbxResults.List(r, 1)
.txtPolNo.Value = lbxResults.List(r, 2)
.txtPostcode.Value = lbxResults.List(r, 3)
.txtCanxDate.Value = lbxResults.List(r, 4)
.txtClaims.Value = lbxResults.List(r, 5)
.txtCanxReason.Value = lbxResults.List(r, 6)
.txtCanxBy.Value = lbxResults.List(r, 7)
.txtComments.Value = lbxResults.List(r, 8)
End With
End If
End Sub
'------------------------------------------'
'------ Update Record ----'
Private Sub cmdUpdateRecord_Click()
Application.ScreenUpdating = False
If rng Is Nothing Then GoTo skip
For Each c In rng
If r = 0 Then c.Select ' <--- Errors Here
r = r - 1
Next c
skip:
Set c = ActiveCell
c.Offset(0, 19).Value = Me.txtCanxBy.Value
With Me
.txtCustomerName.Value = vbNullString
.txtPolNo.Value = vbNullString
.txtPostcode.Value = vbNullString
.txtCanxDate.Value = vbNullString
.txtClaims.Value = vbNullString
.txtCanxReason.Value = vbNullString
.txtCanxDate.Value = vbNullString
.txtComments.Value = vbNullString
End With
If Sheet1.AutoFilterMode Then Sheet1.ShowAllData
Application.ScreenUpdating = True
On Error GoTo 0
End Sub
'------------------------------------------'
'------ Close Form ----'
Private Sub cmdClose_Click()
Unload frmSearch
End Sub

Many thanks for looking,

[Bob Phillips]

It is the same problem as before, you need to select the sheet before selecting the cell.

[phendrena]

It is the same problem as before, you need to select the sheet before selecting the cell.Thanks xld, see, i'm learning (slowly)!

[phendrena]

One other quick thing if you don't mind xld.....
Sorting the data in the userform by Date.

There is the following article in the KB : http://vbaexpress.com/kb/getarticle.php?kb_id=824

Within this article a section of code, named "bubblesort" :-

Sub BubbleSort(MyArray As Variant)

Dim First As Integer
Dim Last As Integer
Dim i As Integer
Dim j As Integer
Dim Temp As String
Dim List As String

First = LBound(MyArray)
Last = UBound(MyArray)
For i = First To Last - 1
For j = i + 1 To Last
If MyArray(i) > MyArray(j) Then
Temp = MyArray(j)
MyArray(j) = MyArray(i)
MyArray(i) = Temp
End If
Next j
Next i
End Sub

Obviously I need to call this routine at some point, but where would you suggest i call this routine within the Search routine section?

Thanks,