CODEWITHSUNDEEP
vbaexcelmacros
George Isaev
George Isaev

Reputation: 1

How do I check whether value in active cell contains any letter or not?

For example cell "A1" is linked to cell "B1", so in formula bar for cell "A1" we have: =B1

How can I check whether value in cell "A1" contains letter B?

I tried the following:

Dim Criteria_3 As Boolean

Dim Value As Range
Set Value = Selection

Dim x As Variant
Set x = Cells

Dim text As String

    For Each x In Value

        If IsNumeric(x) Then

        Criteria_3 = VBA.InStr(1, x.Formula, text) > 0

As soon as value of "Text" is "" it does not work and I really struggle to fined the right solution.

Upvotes: 0

Views: 3692

Answers (4)

David Zemens
David Zemens

Reputation: 53623

Regarding your null string problem:

As soon as value of "Text" is "" it does not work and I really struggle to fined the right solution.

That's because you're using VBA.InStr(1, x.Formula, text) and in this case 1 is an invalid index on a string of length 0. You can omit that, or you can code around it like:

If Len(Trim(x.Formula)) = 0 Then
   '## Do nothing
Else
   Criteria_3 = VBA.InStr(1, x.Formula, text) > 0
End If

To your specific question of identifying when a value contains any alpha character(s):

You can use a function like this to test whether a value contains any letter, by evaluating the Ascii code for each character, and break when True:

Function ContainsAnyLetter(val) As Boolean
Dim ret As Boolean
Dim str$, ch$
Dim i As Long
str = LCase(CStr(val))

For i = 1 To Len(str)
    ch = Mid(str, i, 1)
    If 97 <= Asc(ch) And Asc(ch) <= 122 Then
        ret = True
        Exit For
    End If
Next

ContainsAnyLetter = ret


End Function

enter image description here

In your code, you could call it like:

Criteria_3 = ContainsAnyLetter(x.Value)  '## or x.Formula, depending on your needs

Upvotes: 2

user3598756
user3598756

Reputation: 29421

if your goal is to check whether the cell contains any valid range reference, then you could go like this

Option Explicit

Sub main()

Dim cell As Range

For Each cell In Worksheets("Sheet001").Range("A1:A20") '<== jus a test range, set it as per your needs
    MsgBox IsCellReference(cell.Formula)
Next cell

End Sub

Function IsCellReference(text As String) As Boolean    
On Error Resume Next
IsCellReference = Not Range(Replace(text, "=", "")) Is Nothing
End Function

Upvotes: 0

Nathan_Sav
Nathan_Sav

Reputation: 8531

You can use LIKE

https://msdn.microsoft.com/en-us/library/swf8kaxw.aspx

Something like if rngCell.value like "*B*" then

Upvotes: 1

Sergej
Sergej

Reputation: 376

your question is not really conclusive, so here are two options:

To check wheter the value contains B:

blnCheck = 0 < InStr(1, rngCell.Value, "B")

To check wheter the Formula contains B:

blnCheck = 0 < InStr(1, rngCell.Formula, "B")

Upvotes: 2

Related Questions