Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Finding Text in a spreadsheet

  1. #1
    Guest

    Default

    I am trying to detect whether a block of cells contains text. I am using the following function to do so--it also puts a zero in empty spaces. The GetLastRow fucntion finds the last row containing anything and returns the row number:

    Public Sub FindTextandAddZero()
    Count = 0
    GetLastRow Count
    For Each cell In Range("e2" & ":am" & Count)
    If Not (IsNumeric(cell.Value)) Then
    response = MsgBox("Row number" & " " & cell.row & " " & "contains the following incorrect text:" & " " & "#" & cell & "#" & "." & " " & "Open " & ImportMasterName & ".csv and correct.")
    End If
    Next cell
    For Each cell In Range("a1" & ":am" & Count)
    If cell = Empty Then
    cell.Value = 0#
    End If
    Next cell
    End Sub

    The problem is that the cells are formated as numeric cells and my procedure is not detecting the text. Does anyone no of another way i could accomplish this? Any help would be appreciated. Regards.

  2. #2

    Join Date
    Feb 2002
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this :-

    Public Sub FindTextandAddZero()
    Dim cell As Range
    GetLastRow Count
    On Error Resume Next
    For Each cell In Range("e2" & ":am" & Count).SpecialCells(xlCellTypeConstants, 2)
    MsgBox cell.Address 'plus whatever
    Next
    Range("a2" & ":am" & Count).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "0"
    On Error GoTo 0
    End Sub


    [ This Message was edited by: Autolycus on 2002-03-05 07:14 ]

  3. #3
    New Member
    Join Date
    Mar 2002
    Location
    New York
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    When i run this i get a cells not found error. Would you know why? Thanks. Bill Mahoney

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •