none
Add a search for numeric data only RRS feed

  • Question

  • Hi, I have created this code but want to add a ISNUMERIC data search parameter so it can be used in sheets with alphanumeric data. I have tried adding a while and do loop and another IF statement but can't seem to et it t work. Any suggestions realty appreciated.

    Sub Macro1()
    '
    ' Macro1 Macro
    '
    ' Keyboard Shortcut: Ctrl+q

    ' Stage 1 open file

            Application.FindFile
       
     ' Stage 2 find odd/even Highlight
       
       Dim myData As Range
       Dim myCell As Range
      
       Set myData = ActiveSheet.UsedRange                       ' need to filter data to only search numbers
      
       For Each myCell In myData                                 ' If sheet contains alphanumeric data it fails
      
       If myCell.Value Mod 2 = 0 And myCell.Value > 0 Then
      
       myCell.Interior.ColorIndex = 37
      
       ElseIf myCell.Value Mod 2 = 1 Then
      
       myCell.Interior.ColorIndex = 4
      
       End If
               
        Next

        
    ' Stage 4 Add and Average

        Dim mySheet As Range

        Dim myAverage As Integer

            Set mySheet = ActiveSheet.UsedRange

            With ActiveSheet

                Application.Sum (mySheet)
                myAverage = WorksheetFunction.Average(mySheet)

                    MsgBox "Average is " & myAverage
           
            End With
    End Sub

    Tuesday, October 6, 2015 9:32 PM

Answers