phendrena
11-22-2008, 05:39 AM
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,
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,