none
Type Mis-Match Receving on "Case" statement RRS feed

  • Question

  • hi,

    on first case statement i am receiving type mismatch 13 ,whereas as per this error description this error should occur when we use wrong data type. , please guide.

    Saturday, April 21, 2012 12:00 AM

Answers

  • It's not clear to me yet what you want to accomplish. If you want to search for a value in a range of cells, you can use the Find method:

        Dim rngFound As Range
        Set rngFound = Range("A1:A10").Find(What:="HelloWorld", _
            LookAt:=xlWhole, MatchCase:=False)
        If rngFound Is Nothing Then
            ' value not found
            ...
        Else
            ' value found
            ...
        End If


    Regards, Hans Vogelaar

    • Marked as answer by ANJUM ABBASI Monday, April 23, 2012 10:45 PM
    Monday, April 23, 2012 6:47 PM
  • Are you sure that Sheet1 is valid as the (Name) property of a worksheet (this is not necessarily the same as the Name property)?

    And does your userform contain a text box named TextBox1 ?


    Regards, Hans Vogelaar

    • Marked as answer by ANJUM ABBASI Thursday, May 3, 2012 11:32 PM
    Wednesday, May 2, 2012 7:34 PM

All replies

  • Range("A1:A10") is a multi-cell range object whose value is an array, not a string. If you referred to a single cell, it would work:

        Select Case Range("A1")
            Case "ab"
                Range("B1") = "Name entered"
            ...


    Regards, Hans Vogelaar

    Saturday, April 21, 2012 12:13 AM
  • And for further accuracy I would use:

    Select Case Range("A1").Text


    Rod Gill

    The one and only Project VBA Book Rod Gill Project Management

    Saturday, April 21, 2012 6:30 AM
  • Thanks Mr.Hans for your feedback its really help for me but may be i was unable to clear my point let me rephrase my words

    actually i would like to create a form which will have button named "Search" whose function will to check the Entered emp id's whether they exist in the Excel sheet or not . For that reason i am trying to use Range("A1:A10") with case statement .

    now i have explained complete senario and i hope will get worthful feed back from you.

    Monday, April 23, 2012 4:36 PM
  • It's not clear to me yet what you want to accomplish. If you want to search for a value in a range of cells, you can use the Find method:

        Dim rngFound As Range
        Set rngFound = Range("A1:A10").Find(What:="HelloWorld", _
            LookAt:=xlWhole, MatchCase:=False)
        If rngFound Is Nothing Then
            ' value not found
            ...
        Else
            ' value found
            ...
        End If


    Regards, Hans Vogelaar

    • Marked as answer by ANJUM ABBASI Monday, April 23, 2012 10:45 PM
    Monday, April 23, 2012 6:47 PM
  • Hi Mr. Hans Vogelaar,

    Q: i would like to find value in a Range but receiving eror , please guide.

    once again i am disturbing you need your help as i run he code i rceive Error 424 "Object Required" , what is missing in this Code.

    Monday, April 30, 2012 5:23 PM
  • What is highlighted when you get the error and click Debug?

    Regards, Hans Vogelaar

    Monday, April 30, 2012 7:19 PM
  • 

    Error "424"

    Wednesday, May 2, 2012 5:26 PM
  • Are you sure that Sheet1 is valid as the (Name) property of a worksheet (this is not necessarily the same as the Name property)?

    And does your userform contain a text box named TextBox1 ?


    Regards, Hans Vogelaar

    • Marked as answer by ANJUM ABBASI Thursday, May 3, 2012 11:32 PM
    Wednesday, May 2, 2012 7:34 PM
  • Thanks Mr.Van you are right i was using wrongly name property of textbox. Thanks once again for correction.

    Thursday, May 3, 2012 11:33 PM