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
toRange("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
orRange().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
精彩评论