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

Question

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

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

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:

When trying the code on my system I am getting a match with the function and the TRIM also looks to be working. Can you advise how the values of RSTA_ISIN_clean and logic_String are set? — izzymo
– izzymo, Commented Dec 8, 2015 at 9:40
Hi, sorry for the confusion, I want to check whether RSTA_ISIN is in logic_string or in other words whether logic_string contains RSTA_ISIN — Nant
– Nant, Commented Dec 8, 2015 at 10:34

user4039065 · Accepted Answer · 2015-12-08 10:50:24Z

2

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

answered Dec 8, 2015 at 10:50

user4039065

Sign up to request clarification or add additional context in comments.

Comments

PASUMPON V N · Accepted Answer · 2015-12-08 09:46:34Z

1

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

answered Dec 8, 2015 at 9:46

PASUMPON V N

1,1862 gold badges10 silver badges17 bronze badges

Comments

Fadi · Accepted Answer · 2015-12-08 17:48:21Z

1

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`

answered Dec 8, 2015 at 17:48

Fadi

3,3223 gold badges21 silver badges43 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related