Conditionally Modify text file using VBA

Question

I have a text file as follows that I need to modify using an excel macro. Here is what it looks like (space delimited). Not all of the text file is space delimited however.

"W2"  "S4"  SEC "W1"  PF22 0.7 PRM22 0.7 PI  "P2"  
"W2"  "S3"  SEC "W1"  PF22 0.7 PM22 0.7 PI  "P2"  
"W2"  "S2"  SEC "W1"  PF22 0.7 PM22 0.7 PI  "P2"  
"W2"  "S1"  SEC "W1"  PF22 0.7 PM22 0.7 PI  "P2"

I want to check each row and if the line includes "Sn" and "Pm" equal to a list pairs that I have in my excel file, change the 0.7s values to 0.5.

S1  P2
S2  P1
S5  P1
... ...

I have tried to modify the code in this thread and was unsuccessful.

How should I proceed?

Hello, can we sat there is 1 space between each character set? I mean for example, between "W2" and "S4" 1 space and between PF22 and 0.7 1 space. Is that correct? — Dubison
– Dubison, Commented Feb 24, 2015 at 16:52
and also for Pn and Sn, can n bi bigger then 9? Can it be 2 digits? — Dubison
– Dubison, Commented Feb 24, 2015 at 16:53
Welcome to SO. Please review the how to ask, and post the code you have modified, along with what specifically is the problem you are having. — guitarthrower
– guitarthrower, Commented Feb 24, 2015 at 16:58
Thanks . It has 1 space between PF22 and 0.7. yes n can be bigger than 9. — Dave
– Dave, Commented Feb 24, 2015 at 18:16

Dubison · Accepted Answer · 2015-02-24 17:40:09Z

0

Please see below code. It may not be perfect but it is working based on your data sample in the OP. It is based on chr(34) which is the code for ".

Sub RepStr()
Dim lastrow As Long
Dim srchList As Worksheet
Dim mainList As Worksheet
Dim sStart As Long
Dim sStop As Long
Dim sValue As String
Dim pStart As Long
Dim pSttop As Long
Dim pValue As String

Set srchList = Sheets("Sheet8") '<- Sn Pn list
Set mainList = Sheets("Sheet7") '<- String List

lastrowMain = mainList.Range("A" & Rows.Count).End(xlUp).Row
lastrowsrch = srchList.Range("A" & Rows.Count).End(xlUp).Row

i = 1
While i <= lastrowMain
    'Code based on your string is located at Column A of mainList
    sStart = InStr(5, mainList.Range("A" & i).Value, Chr(34)) + 1
    sStop = InStr(sStart + 1, mainList.Range("A" & i).Value, Chr(34))
    sValue = Mid(mainList.Range("A" & i).Value, sStart, sStop - sStart)

    pStart = InStr(InStr(1, mainList.Range("A" & i).Value, "PI"), mainList.Range("A" & i).Value, Chr(34)) + 1
    pStop = InStr(pStart + 1, mainList.Range("A" & i).Value, Chr(34))
    pValue = Mid(mainList.Range("A" & i).Value, pStart, pStop - pStart)

    'Code based on your matching values are located at srchList Column A (S values), Column B (P values)
    For j = 1 To lastrowsrch
        If srchList.Range("A" & j).Value = sValue And srchList.Range("B" & j).Value = pValue Then
            mainList.Range("A" & i).Value = Replace(mainList.Range("A" & i).Value, 0.7, 0.5)
        End If
    Next j
i = i + 1
Wend
End Sub

answered Feb 24, 2015 at 17:40

Dubison

7665 silver badges12 bronze badges

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

2 Comments

Dave Over a year ago

Another question.Is there any way to start from third " in the string.

Dubison Over a year ago

you mean the 3rd character in the string or 3rd row in the worksheet?

Collectives™ on Stack Overflow

Conditionally Modify text file using VBA

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related