CODEWITHSUNDEEP
excelvbastring
user7078484
user7078484

Reputation: 39

VBA: Loop for string

I am currently looking in a string for a particular value and creating a new string from it. Was wondering if there is any efficient code to do it?

Example:

I have string as:

ISSCD = "ISSUE1; ISSUE2; ISSUE3; ISSUE1; ISSUE3; ISSUE10; ISSUE12; ISSUE2; ISSUE18; ISSUE18; ISSUE1;

but want string as:

NEWISSCD = "ISSUE1; ISSUE2; ISSUE3; ISSUE10; ISSUE12; ISSUE18; "

Here is the code I am using:

Sub test()    
Dim ISSCD, NEWISSCD as String
NEWISSCD = ""
    If InStr(ISSCD, "ISSUE1;") > 0 Then NEWISSCD = NEWISSCD & "ISSUE1; "
    If InStr(ISSCD, "ISSUE2;") > 0 Then NEWISSCD = NEWISSCD & "ISSUE2; "

    '...

    If InStr(ISSCD, "ISSUE50;") > 0 Then NEWISSCD = NEWISSCD & "ISSUE50; "
End Sub

Upvotes: 0

Views: 217

Answers (1)

Tehscript
Tehscript

Reputation: 2556

You can use a dictionary for this purpose. By using dictionary, you can also count have many times your ISSUE# occurs in your original list.

Please see this:

Sub test()
Dim ISSCD()
Dim i As Long
Dim dict As Object
Dim key As Variant
Set dict = CreateObject("Scripting.Dictionary")

ISSCD = Array("ISSUE1", "ISSUE2", "ISSUE3", "ISSUE1", "ISSUE3", "ISSUE10", "ISSUE12", "ISSUE2", "ISSUE18", "ISSUE18", "ISSUE1")

For Each Item In ISSCD
    If dict.Exists(Item) Then
        dict(Item) = dict(Item) + 1
    Else
        dict.Add Item, 1
    End If
Next Item
For Each key In dict.Keys
    Debug.Print key, dict(key)
Next key

End Sub

Upvotes: 2

Related Questions