Loop Macro with isText() function

[drwashngtn]

Hello,

I'm having quite a diffecult time with creating this macro.

What I'm trying to do is loop through a column of cells to check if each one is text or a number and if it is text then conduct an operation. So far my attempts have failed and my Macro gets an error when it reaches the IsText function. this is my macro so far:

Sub LoopTry()

Range("E667").Select

n = 1
test = 0
For Each c In ActiveCell.CurrentRegion.Cells
If (IsText(c.Value) = True) Then test = 1
If (test = 0) Then Next
Else
n = n + 1
Next
.Range("E667") = n


End Sub


Any help that you could give me would be appreciated.

Thank You,

Seth-Elliot Drori

 

[onlyadrafter]

Hello and welcome to the board,

How about this code?

Sub CHECK_FOR_NUMBER_OR_TEXT()
For MY_ROWS = 1 To Range("A65536").End(xlUp).Row
If IsNumeric(Range("A" & MY_ROWS).Value) Then
    MY_ENTRY = MsgBox("NUMBER IN CELL A" & MY_ROWS)
Else
    MY_ENTRY = MsgBox("TEXT IN CELL A" & MY_ROWS)
End If
Next MY_ROWS
End Sub

Change the MY_ENTRY line to your code.

 

[SIXTH SENSE]

Hi!
Try this instead!

If not isnumeric(c.Value) Then test = 1

 

[Norie]

the IsText function

Is this a user defined function?

XL doesn't have an IsText function built in (97 anyway)

 

[Ekim]

IsText is a worksheet function. To use it in VBA, prefix with Application e.g.

Sub macro1()
If Application.IsText(Worksheets("Sheet1").Range("A3")) Then
MsgBox "Yes"
Else
MsgBox "No"
End If

End Sub

On the other hand, “IsNumeric” is a VBA function. As noted by SIXTH SENSE, to test for Text use Isnumeric in the negative:

Sub macro1A()
If Not IsNumeric(Worksheets("Sheet1").Range("A3")) Then
MsgBox "Yes"
Else
MsgBox "No"
End If

End Sub

Regards,

Mike