Vba validate if number, text or spaces

[Joaonep]

Hi guys i am not new to programming but for some time that i dont write nothing. At the moment i am trying to create a small code quit easy i would say and while testing in fake data works but with the real data doesnt.

I have a loop running through all the used cells looking for a specific string althrough i want to grab some info and keep going on each cicle i dont know how to validate if cell contains a double number only, if contains a string and a double number, if is empty or if has spaces......
Using instr i can find the string i need.

Using isempty i detect empty cells

Well with the irregular spaces is my problem so what can i do to detect or validate if that cell contains spaces only?

 

[ZVI]

Hi and welcome to the Board!

Use VarType() function to determine type of value.
For example, type If VarType(Range("A1").Value) and then type equality symbol = to see the list of types.

For checking value with spaces only try this:

  If Len(ActiveCell.Value) > 0 And Len(Trim(ActiveCell.Value)) = 0 Then
    MsgBox "Only spaces are in the active cell"
  End If

If values with spaces should be ignored as well as empty values then:

Sub Test()
  Dim a(), v
  a() = Range("A1:A10").Value
  For Each v In a()
    If Len(Trim(v)) > 0 Then
      ' Nonempty and not only spaces
      Debug.Print v
    End If
  Next
End Sub

 

[Rick Rothstein]

...what can i do to detect or validate if that cell contains spaces only?

Try this...

If UBound(Split(ActiveCell.Value)) = Len(ActiveCell.Value) Then
  ' Cell contains one or more spaces and no other characters
End If