What VBA formula best to use for matching?

[Apple08]

Hi

I am not sure what VBA formula is the best to use for the case below:

Column L is First name
Column M is Last name
Column N is Dept

I need to look for a specific full name, if they match, then the cell next to the name in column N will change to 'Unit'. If they don't match then nothing will be changed. Please could anyone help? Thanks.

 

[AlphaFrog]

Try something like this...

    [color=darkblue]Dim[/color] strFullName [color=darkblue]As[/color] [color=darkblue]String[/color], vNames [color=darkblue]As[/color] [color=darkblue]Variant[/color], i [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    strFullName = "John Smith"
    
    vNames = Range("L1:M" & Range("L:M").Find("*", , , , 1, 2).Row).Value
    
    [color=darkblue]For[/color] i = 1 [color=darkblue]To[/color] [color=darkblue]UBound[/color](vNames, 1)
        [color=darkblue]If[/color] vNames(i, 1) & " " & vNames(i, 2) = strFullName [color=darkblue]Then[/color] Range("N" & i).Value = "Unit": [color=darkblue]Exit[/color] [color=darkblue]For[/color]
    [color=darkblue]Next[/color] i

 

[Apple08]

Many thanks! I forgot to mention that the specific full name is showing more than once in the columns L and M. The formula only changed the first one but not the rest. Please is it possible to edit the formula that can apply changes to all?

 

[AlphaFrog]

Many thanks! I forgot to mention that the specific full name is showing more than once in the columns L and M. The formula only changed the first one but not the rest. Please is it possible to edit the formula that can apply changes to all?

    [COLOR=darkblue]Dim[/COLOR] strFullName [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR], vNames [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR], i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    strFullName = "John Smith"
    
    vNames = Range("L1:M" & Range("L:M").Find("*", , , , 1, 2).Row).Value
    
    [COLOR=darkblue]For[/COLOR] i = 1 [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](vNames, 1)
        [COLOR=darkblue]If[/COLOR] vNames(i, 1) & " " & vNames(i, 2) = strFullName [COLOR=darkblue]Then[/COLOR] Range("N" & i).Value = "Unit"
    [COLOR=darkblue]Next[/COLOR] i

 

[Apple08]

Fantastic! It works perfectly! Many thanks!!!