searching text
searching text
(OP)
Hello kind fellow engineers:
I would like my VBA code to return numeric value for a certain text ending.
For example
It should return 1 for finding .com
It should return 2 for finding .gov
However, the functions that I thought I could use IsLike and Like and Text Extract generate an error that says sub function not defined.
Any advice is much appreciated.
Thanks,
aja
I would like my VBA code to return numeric value for a certain text ending.
For example
It should return 1 for finding .com
It should return 2 for finding .gov
However, the functions that I thought I could use IsLike and Like and Text Extract generate an error that says sub function not defined.
Any advice is much appreciated.
Thanks,
aja





RE: searching text
RE: searching text
If you have access to the help files for the program you are using, you might want to search on 'string operators' or 'string functions'. You will get some good information on
- Left
- Right
- Mid
- InStr
among others.www.nxjournaling.com
RE: searching text
CODE
For VBA, create a module, write this function
CODE
domain = Right(domain, 4)
Select Case domain
Case ".gov"
check_domain = 1
Case ".com"
check_domain = 2
Case Else
check_domain = "neither"
End Select
End Function
RE: searching text
From "BigInch's Extremely simple theory of everything."
RE: searching text
Sub test()
Dim Gov As Byte
Dim Com As Byte
If Application.WorksheetFunction.CountIf(Cells, ".com") > 0 Then
Com = 1
Else
Com = 0
End If
If Application.WorksheetFunction.CountIf(Cells, ".gov") > 0 Then
Gov = 2
Else
Gov = 0
End If
MsgBox "Com = " & Com & ", Gov = " & Gov
End Sub
But if all you really want is to find out if the .com or .gov strings exist in the range or not, you might want to return a TRUE/FALSE instead of a numeric value. If so, this might be better:
Sub test2()
Dim Gov As Boolean
Dim Com As Boolean
If Application.WorksheetFunction.CountIf(Cells, ".com") > 0 Then
Com = True
Else
Com = False
End If
If Application.WorksheetFunction.CountIf(Cells, ".gov") > 0 Then
Gov = True
Else
Gov = False
End If
MsgBox "Com = " & Com & ", Gov = " & Gov
End Sub
The following will return a count of the number of times that the search terms are found:
Sub test3()
Dim Gov As Long
Dim Com As Long
Com = Application.WorksheetFunction.CountIf(Cells, ".com")
Gov = Application.WorksheetFunction.CountIf(Cells, ".gov")
MsgBox "Com = " & Com & ", Gov = " & Gov
End Sub
RE: searching text