searching text 3

[ajaelizabeth]

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

 

[MintJulep]

At a guess, I'd say that you've done something wrong in your code.

 

[cowski]

This link describes some VBA string functions:

http://www.oreilly.de/catalog/lrnwdpr/chapter/ch07_03.html

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
[ul][li]Left[/li]
[li]Right[/li]
[li]Mid[/li]
[li]InStr[/li][/ul]among others.

http://www.nxjournaling.com

 

[chemebabak]

For simple excel code, use:

=IF(RIGHT(A1,4)=".gov",1,IF(RIGHT(A1,4)=".com",2,"neither"))

For VBA, create a module, write this function

Function check_domain(ByVal domain As String)
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

 

[BigInch]

instr is better, since you don't have to fool with defining if you want to look at a certain number of characters on the left or the right side of the string; instr searches the entire string.

From "BigInch's Extremely simple theory of everything."

 

[Hoaokapohaku]

I think this should do:

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

 

[Hoaokapohaku]

My solutions above look in the entire worksheet for the search values. If you want to look in only a portion, even a single cell, change the "cells" in the CountIf function to the limited range that you want to search, e.g. Range("A1") or Range ("A1:G50").