Replace Problems 2

[dnfrantum]

What am I missing here?

Here is the original string:

"029ALLAROAMARKE","P05055900101","HO",20041119,"20041026","","","","","","","","","","","","","560366","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","",20041119,"","","",0,"","","","","029ALLAROAMARKE","P05055900101","LI","","","","","","","","","","","","","","",1,"",105.00,"","","","","","","","","","","","","","",20041119,"","","","","","","","","","","","","","","","17102160",0,"","","","029ALLAROAMARKE","P05055900101","LC","C","G830","","",20.00,"","","","1ZY9309Y0305618895","","200","029ALLAROAMARKE","P05055900101","ST",125.00,"","","","","","","","","","",1,"","","","","","","","",""

Here is my code:

While Not Settle_In_File.AtEndOfStream
            Dim s As String
            s = Settle_In_File.ReadLine
            Debug.Print Settle_In_File.Line
                Settle_Detail_File.Write (Replace(s, ",""029ALLAROAMARKE""", vbCrLf & """029ALLAROAMARKE""")) & (Replace(s, """""029ALLAROAMARKE""", vbCrLf & """029ALLAROAMARKE"""))
       Wend

Here is my output:

"029ALLAROAMARKE","P05055900101","HO",20041119,"20041026","","","","","","","","","","","","","560366","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","",20041119,"","","",0,"","","",""
"029ALLAROAMARKE","P05055900101","LI","","","","","","","","","","","","","","",1,"",105.00,"","","","","","","","","","","","","","",20041119,"","","","","","","","","","","","","","","","17102160",0,"","",""
"029ALLAROAMARKE","P05055900101","LC","C","G830","","",20.00,"","","","1ZY9309Y0305618895","","200"
"029ALLAROAMARKE","P05055900101","ST",125.00,"","","","","","","","","","",1,"","","","","","","","","""029ALLAROAMARKE","P05055900101","HO",20041119,"20041026","","","","","","","","","","","","","560366","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","",20041119,"","","",0,"","","","","029ALLAROAMARKE","P05055900101","LI","","","","","","","","","","","","","","",1,"",105.00,"","","","","","","","","","","","","","",20041119,"","","","","","","","","","","","","","","","17102160",0,"","","","029ALLAROAMARKE","P05055900101","LC","C","G830","","",20.00,"","","","1ZY9309Y0305618895","","200","029ALLAROAMARKE","P05055900101","ST",125.00,"","","","","","","","","","",1,"","","","","","","","","""029ALLAROAMARKE","P05061010102","HO",20041122,"20041029","","","","","","","","","","","","","560366","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","",20041122,"","","",0,"","","",""
"029ALLAROAMARKE","P05061010102","LI","","","","","","","","","","","","","","",1,"",37.00,"","","","","","","","","","","","","","",20041122,"","","","","","","","","","","","","","","","08142770",0,"","",""
"029ALLAROAMARKE","P05061010102","LC","C","G830","","",12.00,"","","","1ZY9309Y0305627625","","200"
"029ALLAROAMARKE","P05061010102","ST",49.00,"","","","","","","","","","",1,"","","","","","","","","""029ALLAROAMARKE","P05061010102","HO",20041122,"20041029","","","","","","","","","","","","","560366","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","",20041122,"","","",0,"","","","","029ALLAROAMARKE","P05061010102","LI","","","","","","","","","","","","","","",1,"",37.00,"","","","","","","","","","","","","","",20041122,"","","","","","","","","","","","","","","","08142770",0,"","","","029ALLAROAMARKE","P05061010102","LC","C","G830","","",12.00,"","","","1ZY9309Y0305627625","","200","029ALLAROAMARKE","P05061010102","ST",49.00,"","","","","","","","","","",1,"","","","","","","","","""029ALLAROAMARKE","P05062460101","HO",20041122,"20041101","","","","","","","","","","","","","560366","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","",20041122,"","","",0,"","","",""

I need to put a vbcrlf before each "029ALLAROAMARKE"
Any help or guidance is appreciated.

Thanks in advance,
Donald

 

[stanmancan]

b = "029ALLAROAMARKE"
a = vbclrf & "029ALLAROAMARKE"
for x = 1 to len(text1.text)
if mid(text1.text,x,15) = b then
b = a
end if
next x

??? just a thought

 

[jebenson]

The problem is in this line:

Settle_Detail_File.Write (Replace(s, ",""029ALLAROAMARKE""", vbCrLf & """029ALLAROAMARKE""")) & (Replace(s, """""029ALLAROAMARKE""", vbCrLf & """029ALLAROAMARKE""")

You are concatenating the entire input line twice. You need to make an intermediate variable, do two separate replaces and then print:

While Not Settle_In_File.AtEndOfStream
Dim s As String
Dim s2 As String
s = Settle_In_File.ReadLine
Debug.Print Settle_In_File.Line
s2 = Replace(s, ",""029ALLAROAMARKE""", vbCrLf & """029ALLAROAMARKE""")
s2 = Replace(s2, """""029ALLAROAMARKE""", vbCrLf & """029ALLAROAMARKE"""))
Settle_Detail_File.Write s2
Wend

Note that in the second replace the variable s2 is the string being searched.

Hope this helps.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[dnfrantum]

I actually did the following and it worked.

While Not Settle_In_File.AtEndOfStream
            Dim s As String
            s = Settle_In_File.ReadLine
            Debug.Print Settle_In_File.Line
               If Settle_In_File.Line = 2 Then
                  Settle_Detail_File.Write (Replace(s, ",029ALLAROAMARKE", vbCrLf & "029ALLAROAMARKE"))
               Else
                  Settle_Detail_File.Write (Replace(Left(s, 17), "029ALLAROAMARKE", vbCrLf & "029ALLAROAMARKE")) & (Replace(Mid$(s, 18), ",029ALLAROAMARKE", vbCrLf & "029ALLAROAMARKE"))
               End If
         Wend

Thanks in advance,
Donald