Need help in VBA code for Instr function

[vinoanbu]

Hi,

I'm working in a Excel Macro, in which the following Instr function taking up the text string(Johan) as partial
but I need to make it as a whole.

If Instr(Cell.value, "Johan,") >0  Or _
Instr(Cell.value, "Johan") >0 Then
cell.offset(0,2).value = "Johan"

For example in cell A1 the text string is "Johan, James, Jason, Johan" for this case the function works fine and returning the value "Johan" in B2
but in Cell A2 the text string is "Johan_VK, Roger" for this case also the function returning the value "Johan" in B2 this is not correct.

could anyone help to improve this Instr section to search as whole text

Regards,
Vino

 

[offthelip]

try this:

If InStr(cell.Value, "Johan,") > 0 Or _
Right(cell.Value, 5) = "Johan" Then
cell.Offset(0, 2).Value = "Johan"
End If

 

[6StringJazzer]

That will work as long as the final name isn't something like VK_Johan. To be certain:

If InStr(cell.Value, "Johan,") > 0 Or Right(cell.Value, 7) = ", Johan" Then
   cell.Offset(0, 2).Value = "Johan"
End If

 

[vinoanbu]

try this:

If InStr(cell.Value, "Johan,") > 0 Or _
Right(cell.Value, 5) = "Johan" Then
cell.Offset(0, 2).Value = "Johan"
End If

Hi,

Thanks for the quick response.
for better understanding please refer the table which is the final output I needed.
the present code taking the text "Johan" as partial one, if it takes as whole text then the problem will be solved. I hope so.

Thanks,
Vino

 

[johnnyL]

InStr is not what you want to use if you are looking for exact matches in your case examples.

Try the following:

    Dim i                   As Long
    Dim SplitStringArray    As Variant
'
    SplitStringArray = Split(Cell.Value, ", ")
'
    For i = 0 To UBound(SplitStringArray)
        If SplitStringArray(i) = "Johan" Then
            Cell.Offset(0, 1).Value = "Johan"
            Exit For
        End If
    Next

 

[vinoanbu]

InStr is not what you want to use if you are looking for exact matches in your case examples.

Try the following:

    Dim i                   As Long
    Dim SplitStringArray    As Variant
'
    SplitStringArray = Split(Cell.Value, ", ")
'
    For i = 0 To UBound(SplitStringArray)
        If SplitStringArray(i) = "Johan" Then
            Cell.Offset(0, 1).Value = "Johan"
            Exit For
        End If
    Next

Hi Johnny,

Thanks for the code. It worked perfect !

Regards,
Vino

 

[johnnyL]

Glad to help.