Checking text & numbers in same cell

[Johnno]

Has anyone an idea of how to check if a row of addresses with text an
numbers in the same cell can be checked to see if they are number only
or text only?
ie addresses like 12 High Street, incorrectly input may only have "12
in the cell, missing the street name, or "High Street", missing th
number.

I'd be going down column I in a sheet and need to isolate and identif
address errors such as this. I'd need the errors flagged -the ro
output onto another sheet. The problem though is checking the addres
to see if it is number only, or text only. I'm new to VBA and can'
seem to pin this down.

Any help would be appreciated. I have been trying to find a solutio
for a few days.

Joh

 

[chandlm]

Hi Johnno,

This should do what you need.

sub check_if_number()
Dim cln As New Collection
a = 1 ' or whatever line you want to start checking from.

Do Until IsEmpty(Range("a" & a).Value)
If IsNumeric(Range("a" & a).Value) Then
cln.Add a


End If
a = a + 1
Loop

Set sht1 = Sheets("sheet1")

Set sht2 = Sheets("sheet2")
r = 1

For Each x In cln
sht1.Rows(x & ":" & x).Select
Selection.Copy Destination:=sht2.Cells(r, 1)

r = r + 1

Next
end su

 

[Tom Ogilvy]

for each cell in Range("A1:A10")
if isnumeric(cell) then
cell.Interior.ColorIndex = 5
end if
Next