VBA macro copies wrong data to cell

This is 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 v开发者_如何学运维ery 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 what I did:

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

With the above macro, the correct value is never pasted into N in some cases. Rather a value from another cell in K is pasted to the wrong cell in N.

I attached an example of excel spreadsheet on the below link to which I never received a response:

http://www.excelforum.com/excel-programming/775695-wrong-data-copied-into-new-cell-from-macro.html

Thanks for response.

Your LValue and LValue2 variables are being populated conditionally (ie, not each time through the loop), but your final block is executed EVERY TIME, so it stands to reason that some times through the loop, you are using an old value of LValue or LValue2 (or both).

You need to clear them out at the beginning of the loop, or else have an ELSE clause in both your LValue and LValue2 IF blocks that takes care of that scenario.

Edit based on your comment: I prefer using MID() to RIGHT() in this scenario, makes it much easier to get the math right, since we're counting from the left (which is the value that InStr() returns):

cellText = Range("K" & i).Value
LValue = Mid(cellText, Unknown + 18, 100)

A few additional notes:

• You use it so many times, put the tested value into a variable like I did above. It might even be marginally faster that way instead of going back to the worksheet each time.
• I prefer to use Cells(11, i).Value to Range("K" & i).Value. Works the same, but much easier to use with variable row or column numbers.
• It usually works the way you've done it, but make sure to use the correct property of the range object (Range().Value or Range().Formula or whatever) instead of just relying on the "default property" to always be correct.

When checking for the underscore, you are testing if the last character is an underscore. Your question states that you want to test if the value begins with an underscore.

schr = Right(LValue, 1)
    If schr = "_" Then

Try

schr = Left(LValue, 1)
    If schr = "_" Then