locked
Find a number greater than RRS feed

  • Question

  • Hi

    I'm trying to find a number greater than 9 in the A column.  When I run this code, it fails when trying to find the values.  I receive a Run Time error '13': type mismatch.

    Also, how can I have it find number values and not text values?

    Thanks in advance

    Zach

     

    Sub FindNumberGreaterThanNine()

    Dim iLoop As Integer

    Dim rNa As Range

    Dim i As Integer

        iLoop = WorksheetFunction.CountIf(Columns(1), ">9")

        Set rNa = Range("A:A")

        For i = 1 To iLoop

            Set rNa = Columns(1).Find(What:=">9", After:=rNa, LookIn:=xlValues, LookAt:=xlWhole, _

                SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=True).Activate

            rNa.Offset(0, 1).Value = iLoop

        Next i

    End Sub

    Monday, April 4, 2011 8:46 PM

Answers

  • hi,

    Set rNa = Range("A:A").SpecialCells(xlCellTypeConstants, 1)

    xlCellTypeConstants
    1      xl number
    2      xlTextValues
    4      xlLogical
    16    xlError

    isabelle
    ------------------------------------------------------------------

    Le 2011-04-05 11:01, ZachTempleton a écrit :

    Thanks Dave.  This sure helps.

    However, I have 70,000 rows of data, and most rows don't contain a number.  Is there a way to just check rows with a number, such as using countif?

    Zach

    • Marked as answer by ZachTempleton Tuesday, April 5, 2011 3:22 PM
    Tuesday, April 5, 2011 3:17 PM

All replies

  • This portion of your code:
    iLoop = WorksheetFunction.CountIf(Columns(1), ">9")
    will count the values greater than 9. 

    =countif() does some things so that it can look at strings and numbers (">" and
    9).

    But this portion of your code:

    Set rNa = Columns(1).Find(What:=">9", After:=rNa, ...

    Is actually looking for the characters ">9" -- not numeric values that are
    greater than 9.  Excel's (and VBA's) Find doesn't work that way.

    And since there isn't a match, the .activate in your code will fail.

    One way to look for those numbers is to search cell-by-cell and inspect each
    cell yourself.

    if application.countif(...) = 0 then
      'don't bother looking
    else
      for each mycell in rna.cells
         if isnumeric(mycell.value) then
             if mycell.value > 9 then
               'do what you want...
         ...

    ZachTempleton wrote:


    Hi

    I'm trying to find a number greater than 9 in the A column.  When I run this code, it fails when trying to find the values.  I receive a Run Time error '13': type mismatch.

    Also, how can I have it find number values and not text values?

    Thanks in advance

    Zach



    Sub FindNumberGreaterThanNine()

    Dim iLoop As Integer

    Dim rNa As Range

    Dim i As Integer

        iLoop = WorksheetFunction.CountIf(Columns(1), ">9")

        Set rNa = Range("A:A")

        For i = 1 To iLoop

            Set rNa = Columns(1).Find(What:=">9", After:=rNa, LookIn:=xlValues, LookAt:=xlWhole, _

                SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=True).Activate

            rNa.Offset(0, 1).Value = iLoop

        Next i

    End Sub

    --

    Dave Peterson

    Monday, April 4, 2011 8:55 PM
  • Thanks Dave.  This sure helps.

    However, I have 70,000 rows of data, and most rows don't contain a number.  Is there a way to just check rows with a number, such as using countif?

    Zach

    Tuesday, April 5, 2011 3:01 PM
  • hi,

    Set rNa = Range("A:A").SpecialCells(xlCellTypeConstants, 1)

    xlCellTypeConstants
    1      xl number
    2      xlTextValues
    4      xlLogical
    16    xlError

    isabelle
    ------------------------------------------------------------------

    Le 2011-04-05 11:01, ZachTempleton a écrit :

    Thanks Dave.  This sure helps.

    However, I have 70,000 rows of data, and most rows don't contain a number.  Is there a way to just check rows with a number, such as using countif?

    Zach

    • Marked as answer by ZachTempleton Tuesday, April 5, 2011 3:22 PM
    Tuesday, April 5, 2011 3:17 PM