Type mismatch using Find statement

brgipple · Apr 15th 2006

I'm using a find statement to locate a text string in a column but I get a 'type mismatch' error when I run it. I'm guessing that it's because of what I'm trying to find is a text and it is looking for a number. Anyway to point to the row number of where I find the macthing text in the column?

Code

'Dimension variables used in Macro.
    Dim Logon As String
    Dim Level As String
    Dim RangeFind As Range
    Set RangeFind = Nothing
    
On Error GoTo Error
    
'Start Access verification
    Logon = Environ("USERNAME")
    Worksheets("Main").Select
    Set RangeFind = ActiveSheet.Columns("S:S").Find(What:=Logon, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If RangeFind Is Nothing Then
        MsgBox "You do not have Access to this WorkBook / WorkSheet", vbOKOnly, "No Access!"
        Set RangeFind = Nothing
        Exit Sub
    End If
    RangeFind.Activate
    Level = ActiveCell.Offset(0, 2)

Display More

the above code works great when what i'm trying to find is a number

thank you
brian

**royUK** · Apr 15th 2006

Re: Type mismatch using Find statement

Try this way

Code

'Dimension variables used in Macro.
Dim Logon As String
Dim Level As String
Dim RangeFind As Range, cl As Range
Set RangeFind = Nothing
 
On Error GoTo Error
 
 'Start Access verification
Logon = Environ("USERNAME")
Worksheets("Main").Select
Set RangeFind = ActiveSheet.Columns("S:S")
With RangeFind
Set cl = .Find(What:=Logon, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If cl Is Nothing Then
    MsgBox "You do not have Access to this WorkBook / WorkSheet", vbOKOnly, "No Access!"
    Set RangeFind = Nothing
    Exit Sub
End If
RangeFind.Activate
Level = ActiveCell.Offset(0, 2)

Display More

brgipple · Apr 16th 2006

Re: Type mismatch using Find statement

Roy
I'm still getting the 'Type Mismatch' error when I run the code you modified for me. What the find function will be looking for is letter number combination like: ABC12345, this is what our PC logon's look like.

Thank you for your help
Brian

**royUK** · Apr 16th 2006

Re: Type mismatch using Find statement

Thjis should work

Code

Option Explicit
Sub ChkLogon()
    'Dimension variables used in Macro.
    Dim Logon  As String
    Dim Level  As String
    Dim RangeFind As Range, cl As Range
    
  On Error GoTo Error
    'Start Access verification
    Logon = Environ("USERNAME")
    'Logon = "ABC12349" test
    Worksheets("Main").Select
    Set RangeFind = ActiveSheet.Columns("S:S")
    With RangeFind
        Set cl = .Find(Logon, LookIn:=xlValues)
        If cl Is Nothing Then
            MsgBox "You do not have Access to this WorkBook / WorkSheet", vbOKOnly, "No Access!"
            Set RangeFind = Nothing
            Exit Sub
        End If
    End With
    Level = cl.Offset(0, 2).Value
    MsgBox Level


    Set RangeFind = Nothing
    Set cl = Nothing
    Exit Sub
Error:
    MsgBox "Error occurred"
End Sub

Display More

brgipple · Apr 18th 2006

Re: Type mismatch using Find statement

Roy

Thanks the new code worked. Any idea why i was getting the 'Type Mismatch' error? Would like to know so i can avoid future issues and having to ask for help.

Thanks again

Brian G.

Type mismatch using Find statement

Participate now!

Share

Users Viewing This Thread