Hello again, Ozgridians,
I am trying to run a simple search and move function, looping through column B; on every cell in Column B, it loops through Column A, if it finds a matching value, it copies the values from Sheet1(RawData) to column A and B on Sheet2(Clean), then deletes the cells with a shift up. It is looping from the bottom to the top, but it keeps skipping certain values, no matter how many times the script is run.
VBA code:
Sub Cleanup()
Set rd = Sheets("RawData")
Set pd = Sheets("Clean")
rup = rd.Range("B2500").End(xlUp).Row ' set rup to the last row in B Column on RawData
pup = rd.Range("A2500").End(xlUp).Row ' set pup to the last row in A Column on RawData
For i = rup To 2 Step -1
For j = pup To 2 Step -1
If (rd.Cells(i, 2)) = (rd.Cells(j, 1)) Then
Set NumVal = rd.Cells(i, 2)
pd.Cells(pd.Range("A2500").End(xlUp).Row + 1, 1) = NumVal
pd.Cells(pd.Range("B2500").End(xlUp).Row + 1, 2) = NumVal
rd.Cells(i, 2).Delete Shift:=xlUp
rd.Cells(j, 1).Delete Shift:=xlUp
Exit For
End If
Next j
Next i
End Sub
Display More
And a copy of the workbook as it stands now.
I just need to see why it is skipping values... they are not obviously different(text in one column, number in the other), and they are not being copied but not deleted, so I'm at a loss. Sheet 3 contains a copy of the original number list so I can just copy it in and re-test my code.
For some odd reason, I cannot upload my workbook. I'll keep trying to update this post with the book.
Edit: Had to upload it as a zip file, the main file is 26 KB too large. Virus scan and all that stuff before you play with it, never know what kind of nasties are flying around.