ExcelBanter - User Form Error Excel 2003!

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - User Form Error Excel 2003! (https://www.excelbanter.com/excel-programming/377304-user-form-error-excel-2003-a.html)

User Form Error Excel 2003!

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("D2:D24000")
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

User Form Error Excel 2003!

User Form Error Excel 2003!

User Form Error Excel 2003!

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

jfcby wrote:
Hello,

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

Compile error
Invalid mismatch

and highlighted

SelRange

Thanks,
James Cooper

Jim Rech wrote:
Perhaps:

oSearch.Activate

--
Jim
"jfcby" wrote in message
ups.com...
| 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("D2:D24000")
| 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
|