CODEWITHSUNDEEP
stringexcelvbacomparestring-comparison
Nant
Nant

Reputation: 569

VBA compare string ( check if string one contains string 2)

I am having a problem with comparing two strings. The method does not seem to work somehow. I have tried the following to functions:

  1. Method: 

    If StrComp(logic_string, RSTA_ISIN_clean) Then
    rng.Offset(0, 16) = "OK(ISIN in RSTA)"
    rng.Offset(0, 16).Interior.Color = 5296274
Else
    rng.Offset(0, 16) = "NG(ISIN not RSTA or check RSTA)"
    rng.Offset(0, 16).Interior.Color = 255
End If

  2. Method: 

     If InStr(1, RSTA_ISIN_clean, logic_string, vbTextCompare) Then
     rng.Offset(0, 16) = "OK(ISIN in RSTA)"
     rng.Offset(0, 16).Interior.Color = 5296274
 Else
     rng.Offset(0, 16) = "NG(ISIN not RSTA or check RSTA)"
     rng.Offset(0, 16).Interior.Color = 255
 End If

in logic_string I have the value "FR0012915700" and in RSTA_ISIN i have the value = " Old ISIN: FR0012915700"

All I am trying to do is to check if RSTA_ISIN is in logic_string and if so i want to write OK in cell. (trying to get a contains method here)

It can be that something is wrong with logic_string as it sometimes gives me spaces -> so logic_string looks like this in debug mode " FR0004052561" -> I tried to trim the spaces with Trim but that doesn't work either.

I have also tried the InStr function but that doesn't work either

Can someone assist please

This is what I get in debug mode:

enter image description here

Upvotes: 2

Views: 2507

Answers (3)

Fadi
Fadi

Reputation: 3322

Try to use Like Operator:

Dim logic_string As String, RSTA_ISIN_clean As String

  logic_string = " FR0012915700"
  RSTA_ISIN_clean = " Old ISIN: FR0012915700"


 If RSTA_ISIN_clean Like "*" & Trim(logic_string) & "*" Then
    Rng.Offset(0, 16) = "OK(ISIN in RSTA)"
    Rng.Offset(0, 16).Interior.Color = 5296274
 Else
    Rng.Offset(0, 16) = "NG(ISIN not RSTA or check RSTA)"
    Rng.Offset(0, 16).Interior.Color = 255
 End If`

Upvotes: 1

user4039065
user4039065

Reputation:

It doesn't sound to me that you actually want to use the StrCmp function at all; it sounds as if your find-string-within-another-string should be handled by the InStr function. Please click those links and read the MSDN documentation to make a decision.

Dim logic_string as String, RSTA_ISIN_clean as String

logic_string = "FR0012915700"
RSTA_ISIN_clean = " Old ISIN: FR0012915700"

If CBool(InStr(1, RSTA_ISIN_clean, logic_string, vbTextCompare)) Then
    rng.Offset(0, 16) = "OK(ISIN in RSTA)"
    rng.Offset(0, 16).Interior.Color = 5296274
Else
    rng.Offset(0, 16) = "NG(ISIN not RSTA or check RSTA)"
    rng.Offset(0, 16).Interior.Color = 255
End If

Upvotes: 2

PASUMPON V N
PASUMPON V N

Reputation: 1186

Please check your variable. "RSTA_ISIN_clean" or "RSTA_ISIN" as variable

Sub test1()

Dim RSTA_ISIN_clean As String
Dim logic_string As String
Dim Rng As Range


Set Rng = ActiveSheet.Cells(1, 30)

logic_string = "FR0012915700"

RSTA_ISIN_clean = "Old ISIN: FR0012915700"

logic_string = Trim(logic_string)
RSTA_ISIN_clean = Trim(RSTA_ISIN_clean)


If StrComp(logic_string, RSTA_ISIN_clean) Then
    Rng.Offset(0, 16) = "OK(ISIN in RSTA)"
    Rng.Offset(0, 16).Interior.Color = 5296274
Else
    Rng.Offset(0, 16) = "NG(ISIN not RSTA or check RSTA)"
    Rng.Offset(0, 16).Interior.Color = 255
End If



End Sub

Upvotes: 1

Related Questions