VBA: Loop for string

Question

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

Are the strings you are looking for always "ISSUE" followed by a number, or is that just example data you are using for the question? (If it was the real data, I would just do much the same as you but in a loop. If it is just sample data then I would use a different approach.) — YowE3K
– YowE3K, Commented Apr 29, 2017 at 21:19
You can use an array for this. If your secret question is "How can I get unique values out of my list?" then your secret answer is dictionary. — Tehscript
– Tehscript, Commented Apr 29, 2017 at 21:27
Using your method, one side-advantage is that the resulting list will always be sorted, even if the input list doesn't have first-mentions in sorted order. Is a sorted list a requirement? Is it possible to have an input list with first-mentions not sorted? — Rich Holton
– Rich Holton, Commented Apr 29, 2017 at 21:44

Tehscript · Accepted Answer · 2017-04-29 21:45:43Z

2

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

answered Apr 29, 2017 at 21:45

Tehscript

2,5562 gold badges15 silver badges24 bronze badges

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

Collectives™ on Stack Overflow

VBA: Loop for string

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related