Q: Conditionally Modify text file using VBA 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?

A: 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

