wrong data copied into new cell from macro

Hey all,

I have this macro and this is basically what I am trying to do:

If J contains the word Defendant
And
If F contains the word Foreclosure
Then
If G contains " V ESTATE OF "
Then keep everything to the right of "OF"
Else If G contains " VS "
Then keep everything to the right of " VS "
Else If G contains " V " (notice the spaces before and after V)
Then keep everything to the right of " V "

If K contains " " (two consecutive spaces)
Then Keep it
Or
If K contains "UNKNOWN SPOUSE OF"
Then remove the very last character of cell, which will be a comma
And if the cell begins with an underscore
Then remove it
Then Keep it

Assign the result of G to the corresponding N cell
Assign the result of K to the corresponding O cell

This is macro:

Sub Inspect()
 
Dim RENums As Object
Dim RENums2 As Object
Dim LValue  As String
Dim LValue2  As String
 
 
Set RENums = CreateObject("VBScript.RegExp")
Set RENums2 = CreateObject("VBScript.RegExp")
 
 
RENums.Pattern = "DEFENDANT"
RENums2.Pattern = "FORECLOSURE"
 
 
  Dim lngLastRow As Long
  lngLastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
  
 
  Dim i
 
  For i = 1 To lngLastRow
 
    If RENums2.test(Range("F" & i).Value) Then
 
      If RENums.test(Range("J" & i).Value) Then
   
          pos = InStr(Range("G" & i), " V ")
         
          pos2 = InStr(Range("G" & i), " VS ")
          
          pos3 = InStr(Range("G" & i), " V ESTATE OF ")
 
          dbspace = InStr(Range("K" & i), "  ")
   
          If pos3 <> 0 Then
             LValue2 = Right(Range("G" & i), Len(Range("G" & i)) - pos * 2)
          ElseIf pos <> 0 Then
             LValue2 = Right(Range("G" & i), Len(Range("G" & i)) - pos - 2)
          ElseIf pos2 <> 0 Then
            LValue2 = Right(Range("G" & i), Len(Range("G" & i)) - pos - 2)
          End If
 
          If dbspace <> 0 Then
            LValue = Range("K" & i)
          End If
 
          
            schr = Right(LValue, 1)
            If schr = "_" Then
              With WorksheetFunction
               Range("N" & i).Value = Trim(.Substitute(LValue, "_", ""))
              End With
            Else
              Range("N" & i).Value = Trim(LValue)
            End If
            Range("O" & i).Value = Trim(LValue2)
          
           
      End If
    End If
 
  Next i
 
End Sub

What happens right now is something strange. The correct value is never pasted into N in some cases. Rather a value from another cell in K is pasgted to the wrong cell in N.

I attached an excel spreadsheet example of the issue.

Thanks for response.

Try this:

Option Explicit

Sub Inspect()
Dim LValue  As String, LValue2  As String
Dim lngLastRow As Long, i As Long

lngLastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
  
For i = 1 To lngLastRow
    If InStr(Range("F" & i), "FORECLOSURE") Then
        If InStr(Range("J" & i), "DEFENDANT") Then
            If InStr(Range("G" & i), " V ESTATE OF ") <> 0 Then
                Range("N" & i) = Trim(Mid(Range("G" & i), InStr(Range("G" & i), " V ESTATE OF ") + 12, 100))
            ElseIf InStr(Range("G" & i), " V ") <> 0 Then
                Range("N" & i) = Trim(Mid(Range("G" & i), InStr(Range("G" & i), " V ") + 3, 100))
            ElseIf InStr(Range("G" & i), " VS ") <> 0 Then
                Range("N" & i) = Trim(Mid(Range("G" & i), InStr(Range("G" & i), " VS ") + 4, 100))
            End If
 
            If InStr(Range("K" & i), "  ") <> 0 Then
                Range("O" & i) = Range("K" & i)
            Else
                If InStr(Range("K" & i), "UNKNOWN SPOUSE") > 0 Then
                    Range("O" & i) = Trim(Replace(Left(Range("K" & i), Len(Range("K" & i)) - 1), "_", ""))
                Else
                    Range("O" & i) = Trim(Replace(Range("K" & i), "_", ""))
                End If
            End If
        End If
    End If
Next i
 
End Sub

The problem was you hadn't put in the UNKNOWN SPOUSE stuff anywhere, and you were only filling in your LValue variable when there were dblspaces. Since most of your variables were only being referenced one time, I took a bunch out and issued commands directly against the one-time calculations.

Also, based on your final instructions above, you had the wrong information going into N and O, backwards.