Testing for text using VBA

[RJQMAN]

I have been experimenting trying to find a way to use VBA to test to
see if a user has entered text into a cell.

I have tried Len and istext without success - perhaps I am not doing
it correctly, I am not sure. However through trial and error I found
something that seems to work - I just want to be sure that it is not a
fluke (no insult to the Fluke Mfg. company intended).

I am trying in this example to determine if there is text in cell
E14. I found that if I compare the value cell E14 to a positive
integer (and then place a numerical value in cell G14 just as a test),
it seems to work as follows;

If Range("E14").Value < 1 Then Range("G14").Value = 7

I find that if cell E14 has text in it, then the formula runs and G7
remains blank. If there is text in E14, then the formula result is
that G7 returns the value of 7. I have tried this with several
entries, and it seems to work. The cell E14 is set to 'General' by
the way.

I do not understand why it works. Is this going to work all of the
time, or is it just a fluke? I would appreciate knowing if I am
creating a problem for myself in the future if I use this test in a
VBA program. Can someone tell me if what I am doing is going to be
reliable and work in the future?

Thanks in advance, again...

 

[smartin]

I have been experimenting trying to find a way to use VBA to test to
see if a user has entered text into a cell.

I have tried Len and istext without success - perhaps I am not doing
it correctly, I am not sure. However through trial and error I found
something that seems to work - I just want to be sure that it is not a
fluke (no insult to the Fluke Mfg. company intended).

I am trying in this example to determine if there is text in cell
E14. I found that if I compare the value cell E14 to a positive
integer (and then place a numerical value in cell G14 just as a test),
it seems to work as follows;

If Range("E14").Value < 1 Then Range("G14").Value = 7

I find that if cell E14 has text in it, then the formula runs and G7
remains blank. If there is text in E14, then the formula result is
that G7 returns the value of 7. I have tried this with several
entries, and it seems to work. The cell E14 is set to 'General' by
the way.

I do not understand why it works. Is this going to work all of the
time, or is it just a fluke? I would appreciate knowing if I am
creating a problem for myself in the future if I use this test in a
VBA program. Can someone tell me if what I am doing is going to be
reliable and work in the future?

Thanks in advance, again...

"If you can hear this, it must be a fluke" LOL

Try testing your logic with input like 123abc.

I think a better way would be
If Not IsNumeric(Range("E14").Value) Then
' is (or contains) text

 

[RJQMAN]

"If you can hear this, it must be a fluke" LOL

Try testing your logic with input like 123abc.

I think a better way would be
   If Not IsNumeric(Range("E14").Value) Then
     ' is (or contains) text- Hide quoted text -

- Show quoted text -

Thanks for your reply. I did test it as you suggest, and it still
seemed to work fine. The user will be entering the names of high
schools, and they will always start with text, by the way. The
program calculates scores of a high school interscholastic band
competition, and I want the program to see if there is a school name
entered into the cell. The names are things like "Citrus High
School." They will never be numeric, as far as I can envision, but I
tried the logic with the quasi-numeric entry, and with entries that
have a space in the first character, and it still seems to work.

I am such a novice that I do not understand the logic you have
suggested - I am self taught, and I know more every minute I work,
but I have barely scratched the surface. I want to take a class from
someone who really knows what they are doing one of these days. Whew.

It is great to have someone else look at this for me, and I cannot
tell you how much I appreciate your taking the time and effort to
respond. Hmm. I think I hear something... must be that fluke,falling
in the forest. I wonder if every fluke is really a diferent shape?
Or was that flakes? Hmm. LOL.

Thanks again.

 

[smartin]

Thanks for your reply. I did test it as you suggest, and it still
seemed to work fine. The user will be entering the names of high
schools, and they will always start with text, by the way. The
program calculates scores of a high school interscholastic band
competition, and I want the program to see if there is a school name
entered into the cell. The names are things like "Citrus High
School." They will never be numeric, as far as I can envision, but I
tried the logic with the quasi-numeric entry, and with entries that
have a space in the first character, and it still seems to work.

I am such a novice that I do not understand the logic you have
suggested - I am self taught, and I know more every minute I work,
but I have barely scratched the surface. I want to take a class from
someone who really knows what they are doing one of these days. Whew.

It is great to have someone else look at this for me, and I cannot
tell you how much I appreciate your taking the time and effort to
respond. Hmm. I think I hear something... must be that fluke,falling
in the forest. I wonder if every fluke is really a diferent shape?
Or was that flakes? Hmm. LOL.

Thanks again.

NP, and welcome to the world of VBA. I too am self-taught, thanks
primarily to the internet!

Curious, my example breaks your algorithm in my test. Not sure why it
works for you. But if indeed your data never has leading numerals, you
should be fine.

Classes are good, but IME there is nothing so educational as a well
executed tutorial. If you're hanging around, do follow the links posted
by some of the regulars. By and large, they have excellent blogs and/or
sites, with links to more of the same.

And if you would like more detailed explanations about anything VBA, do
feel free to ask here. I'm sure it will be relevant despite the "Excel"
designation of the group.

Good luck!