User Form Error Excel 2003!

[jfcby]

Hello,

I'm tring to create a User Form to select a range and find data within
cells in the selected range. But I'm getting this error message

Run-time error '13'
Type mismatch

then it highlights this part of the code:

SelRange(oSearch.Address).Activate ' or whatever

How can the code be changed work without errors?

The complete code:

Private Sub CommandButton1_Click()

Dim SelRange As Range
Dim Addr As String
Dim oSearch As Object
Dim sFind As String

'Get the address, or reference, from the RefEdit control.
Addr = RefEdit1.Value

'Set the SelRange Range object to the range specified in the
'RefEdit control.
Set SelRange = Range(Addr)

'Apply a red pattern to the SelRange.
'SelRange.Interior.ColorIndex = 3

'Finds the information
sFind = TextBox1.Value 'InputBox("Enter search criteria:", "Data")

With SelRange 'ActiveSheet.Range("D224000")
Set oSearch = .Find(sFind, , xlValues)
If Not oSearch Is Nothing Then
SelRange(oSearch.Address).Activate ' or whatever
Else
MsgBox "No match could be found"
End If
End With

'Unload the userform.
Unload Me

End Sub

Thanks in advsnce for your help,
James Cooper

 

[Jim Rech]

Perhaps:

oSearch.Activate

--
Jim
| Hello,
|
| I'm tring to create a User Form to select a range and find data within
| cells in the selected range. But I'm getting this error message
|
| Run-time error '13'
| Type mismatch
|
| then it highlights this part of the code:
|
| SelRange(oSearch.Address).Activate ' or whatever
|
| How can the code be changed work without errors?
|
| The complete code:
|
| Private Sub CommandButton1_Click()
|
| Dim SelRange As Range
| Dim Addr As String
| Dim oSearch As Object
| Dim sFind As String
|
| 'Get the address, or reference, from the RefEdit control.
| Addr = RefEdit1.Value
|
| 'Set the SelRange Range object to the range specified in the
| 'RefEdit control.
| Set SelRange = Range(Addr)
|
| 'Apply a red pattern to the SelRange.
| 'SelRange.Interior.ColorIndex = 3
|
| 'Finds the information
| sFind = TextBox1.Value 'InputBox("Enter search criteria:", "Data")
|
| With SelRange 'ActiveSheet.Range("D224000")
| Set oSearch = .Find(sFind, , xlValues)
| If Not oSearch Is Nothing Then
| SelRange(oSearch.Address).Activate ' or whatever
| Else
| MsgBox "No match could be found"
| End If
| End With
|
| 'Unload the userform.
| Unload Me
|
| End Sub
|
| Thanks in advsnce for your help,
| James Cooper
|

 

[jfcby]

Hello,

NO the oSearch.Activate did no work. It give me the error message

Compile error
Invalid mismatch

and highlighted

SelRange

Thanks,
James Cooper

 

[jfcby]

Hello,

Thank you for your help!

I figured out what my problem was and here is the complete working
code:

Private Sub CommandButton1_Click()
'code works

Dim SelRange As Range
Dim Addr As String
Dim oSearch As Object, sFind As String

'Get the address, or reference, from the RefEdit control.
Addr = RefEdit1.Value

'Set the SelRange Range object to the range specified in the
'RefEdit control.
Set SelRange = Range(Addr)

'Apply a red pattern to the SelRange.
'SelRange.Interior.ColorIndex = 3

'Finds the information
sFind = TextBox1.Value

With SelRange
Set oSearch = .Find(sFind, , xlValues)
If Not oSearch Is Nothing Then
oSearch.Select
Else
MsgBox "No match could be found"
End If
End With

'Unload the userform.
Unload Me

End Sub

Thanks,
jfcby