×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

searching text
3

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

RE: searching text

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

RE: searching text

For simple excel code, use:

CODE

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

For VBA, create a module, write this function

CODE

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

 

RE: searching text

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."

RE: searching text

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
 

RE: searching text

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").

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources