Finding Text in a spreadsheet

[Guest]

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.

 

[Legacy 344]

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

 

[Madingo]

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