If(isnumber(search) vba

[rakesh seebaruth]

HI Guys

i have the following table

I
L S
Mr John Smith
Mr Jack Paul and Mrs Jack Paul


my vba codes are as follows:-

Sub x()
Dim TR As Long
TR = Cells(Rows.Count, "I").End(xlUp).Row
Range("L2:L" & TR) = Evaluate("IF(ISNUMBER(SEARCH(""MR"",I2:I" & TR & ")),""Dear Sir"","""")")
Dim SS As Long
SS = Cells(Rows.Count, "L").End(xlUp).Row
Range("S2:S" & SS) = Evaluate("IF(ISNUMBER(SEARCH(""Dear Sir"",L2:L" & SS & ")),""your banking facility"","""")")
End Sub


What i want is

if in column I there are Mr and Mrs then column L= Dear Sir/Madam. and if L= Dear Sir/Madam then Column S= your banking facilities.
With Mr its works well.

Thanks for your help

regards

rakesh

 

[pbornemeier]

This might suit:

Sub Revised()
    
    Dim TR As Long
    Dim rngCell As Range
    Dim bMR As Boolean
    Dim bMRS As Boolean
    
    TR = Cells(Rows.Count, "I").End(xlUp).Row
    
    For Each rngCell In Range("I1:I" & TR)
        bMR = False: bMRS = False
        Debug.Print rngCell.Value
        If InStr(UCase(rngCell.Value), "MR ") > 0 Then bMR = True
        If InStr(UCase(rngCell.Value), "MR.") > 0 Then bMR = True
        If InStr(UCase(rngCell.Value), "MRS ") > 0 Then bMRS = True
        If InStr(UCase(rngCell.Value), "MRS.") > 0 Then bMRS = True
        
        If bMR And bMRS Then
            rngCell.Offset(0, 3).Value = "Dear Sir/Madam"
            rngCell.Offset(0, 10).Value = "your banking facilities"
        ElseIf bMR Then
            rngCell.Offset(0, 3).Value = "Dear Sir"
            rngCell.Offset(0, 10).Value = "your banking facility"
        ElseIf bMRS Then
            rngCell.Offset(0, 3).Value = "Dear Madam"
            rngCell.Offset(0, 10).Value = "your banking facility"
        End If
    Next
End Sub

 

[Jan Mach]

This is sooo complicated...
You have no reason to calculate UCase(rngCell.Value) and rngCell.Offset(0, 3) many times.
You have no reason to use the variant Value property and variant UCase function when you need a string.
Write simply

Const er = "A creature with no sex found!" & vbLf & "Row: "
Dim rngCell As Range, ucStr$, i&
For Each rngCell In [I1].Resize(Cells(Rows.Count, "I").End(xlUp).Row)
    With rngCell
        ucStr=UCase$(.Text)
[LEFT]        i = Sgn(InStr(ucStr, "MR ") + InStr(ucStr, "MR.")) * 2 + Sgn(InStr(ucStr, "MRS"))[/LEFT]
        If i Then
            .Offset(, 3) = Choose(i, "Dear Madam", "Dear Sir", "Dear Madam/Sir")
[LEFT][COLOR=#333333][FONT=monospace][LEFT][COLOR=#222222][FONT=Verdana]            .Offset(, 10) = "your banking facilit" [COLOR=#222222][FONT=Verdana]&[/FONT][/COLOR] IIf(i = 3, "ies", "y")
        Else
            MsgBox er [COLOR=#222222][FONT=Verdana]&[/FONT][/COLOR] .Row, , "Error dude"
        End If
    End With
Next
[/FONT][/COLOR][/LEFT]
[/FONT][/COLOR][/LEFT]

(It's not my fault, it's a fault of the idiot who programmed this editor that is not able to keep formatting and presents a dud as my work.)

 

[RoryA]

Again, Value is faster than Text.

 

[pbornemeier]

@Jan Mach - Thanks for the optimization tips. I had not known of/remembered the SGN function.

@RoryA - Is there a master list somewhere that shows relative speed of Excel functions? (Both for VBA and Formulas) Google does not bring up anything organized that I could locate.

 

[RoryA]

No, not that I'm aware of.