Finding Text in a spreadsheet

G

Guest

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.
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top