Solved

VB6 string replace

Posted on 2014-12-02
12
224 Views
Last Modified: 2015-01-28
So I have an issue that I cannot figure out.

I have a string that contains email addresses

I.E
test@test.com, test@123.com, test@456.com

Open in new window


I have another string set of
 I.E
test@123.com, test@test.com

Open in new window


I want to remove the second string set from string 1. Even though they are not in order.  I don't know how to figure this one out.  I would assume that i need to separate the second string into an array perhaps and do a foreach loop which removes them from string one.

Scratching my head at this moment.
0
Comment
Question by:desiredforsome
  • 6
  • 5
12 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40476271
what about:
Dim s1 as String 
Dim s2 as String 
s1 =  "test@test.com, test@123.com, test@456.com"
s2 = "test@123.com, test@test.com"

Dim arr() As String = Split(s2, ",")
Dim i as Long
s1 = replace(s1, " ", "")
i = lbound(arr)
while i <= ubound(arr)
begin
   s1 = replace(s1, arr(i) & ",", "")
   i = i + 1
end

Open in new window

0
 
LVL 5

Expert Comment

by:Hakan Yılmaz
ID: 40476308
This may help.

Sub Remove()

    Const delimiter As String = ","

    Dim iterStr As Variant
    Dim mainlist As String
    Dim sublist As String
    Dim split_sublist() As String

    mainlist = "test@test.com, test@123.com, test@456.com"
    sublist = "test@123.com, test@test.com"
    
    split_sublist = Split(sublist, delimiter)
    
    For Each iterStr In split_sublist

        'clean iterStr the way you want.
        iterStr = Replace(iterStr, " ", "")

        mainlist = Replace(mainlist, iterStr & delimiter, "")
        mainlist = Replace(mainlist, iterStr, "")

    Next iterStr
    
    'clean mainlist the way you want.
    mainlist = Trim(mainlist)

End Sub

Open in new window

0
 

Author Comment

by:desiredforsome
ID: 40476804
For Hakan, I get a Mismatch on the FOr each line

For Guy -

I dont see where this outputs to a string. Only an array.
0
 

Author Comment

by:desiredforsome
ID: 40476808
Also get unexptected text on Guy.
0
 
LVL 5

Expert Comment

by:Hakan Yılmaz
ID: 40476819
Can you please write me what error is? I don't have vb6 installed. I try it in excel VBA.
Also please change "Dim iterStr As Variant" to "Dim iterStr" and try.
0
 

Author Comment

by:desiredforsome
ID: 40476824
So the whole error is Error: 10080 (10080) Type Mismatch Line number 17. This is also after I have changed the dimension.

It is on the "For Each" line of code.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 5

Expert Comment

by:Hakan Yılmaz
ID: 40476833
Can you please now change it to "Dim iterStr As String" and try?
0
 

Author Comment

by:desiredforsome
ID: 40476845
I get Error 0 Var is already defined as a different type line 17 (that is on the For Each line)
0
 
LVL 5

Expert Comment

by:Hakan Yılmaz
ID: 40476856
Please try this.

Sub Remove()

    Const delimiter As String = ","

    Dim mainlist As String
    Dim sublist As String
    Dim split_sublist() As String

    mainlist = "test@test.com, test@123.com, test@456.com"
    sublist = "test@123.com, test@test.com"
    
    split_sublist = Split(sublist, delimiter)
    
    For Each iterStr as String In split_sublist

        'clean iterStr the way you want.
        iterStr = Replace(iterStr, " ", "")

        mainlist = Replace(mainlist, iterStr & delimiter, "")
        mainlist = Replace(mainlist, iterStr, "")

    Next iterStr
    
    'clean mainlist the way you want.
    mainlist = Trim(mainlist)

End Sub

Open in new window

0
 

Author Comment

by:desiredforsome
ID: 40476861
Error 0
Expeting 'In' line number 16   (Same line as always). I am try to figure this out with research and and pulling hair out.
0
 
LVL 5

Accepted Solution

by:
Hakan Yılmaz earned 500 total points
ID: 40476881
VBA and VB are very similiar, i have mixed syntax.

Changed code to loop between bounds. Please try this.

Sub Remove()

    Const delimiter As String = ","

    Dim i As Integer
    Dim iterStr As String
    Dim mainlist As String
    Dim sublist As String
    Dim split_sublist() As String

    mainlist = "test@test.com, test@123.com, test@456.com"
    sublist = "test@123.com, test@test.com"
    
    split_sublist = Split(sublist, delimiter)
    
    For i = LBound(split_sublist) To UBound(split_sublist)

        iterStr = split_sublist(i)

        'clean iterStr the way you want.
        iterStr = Replace(iterStr, " ", "")

        mainlist = Replace(mainlist, iterStr & delimiter, "")
        mainlist = Replace(mainlist, iterStr, "")

    Next i
    
    'clean mainlist the way you want.
    mainlist = Trim(mainlist)

End Sub

Open in new window

0
 

Author Closing Comment

by:desiredforsome
ID: 40476894
OMG AWESOME!!!!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now