VBA Loop Not Working Properly

[Michael515]

Hi Y'all,

The code below inserts a matching function, to match cell values from one cell in Sheet1 to an array in Sheet 2. Then I created a loop that if the Match value > 0, to then take the whole row of that "i" value and paste into sheet 3. For some reason my loop isn't looking at all the M & "i" cells because I have two cells that have a value greater than 0. I'd appreciate you're help, especially because I feel like I'm right there. Thanks again!

Sub SortData()
    Sheets("Sheet1").Select
    
    'Find last row in a dataset
    Dim Lastrow As Long
    Lastrow = Cells(Rows.Count, "A").End(xlUp).row
    
    Range("M2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(MATCH(RC[-2], 'Sheet2'!R2C8:R1048576C8, 0), """")"
    Range("M2").AutoFill Destination:=Range("M2:M" & Lastrow)
    Columns("M:M").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    For i = 2 To Lastrow
        
        If Sheets("Sheet1").Range("M" & i).Value > 0 Then
        
        Else
            Sheets("Sheet1").Select
            Rows(i).Copy
            Sheets("Sheet3").Select
                Dim Lastrow2 As Long
                Lastrow2 = Cells(Rows.Count, "A").End(xlUp).row
                Dim Lastrow3 As Long
                Lastrow3 = Lastrow2 + 1
            Rows(Lastrow3).Select
            Selection.PasteSpecial Paste:=xlPasteValues
            Application.CutCopyMode = False
        End If
        
        Next i
End Sub

 

[Michael515]

I'm getting a mismatch error, does that tell you anything, or is that something on my end that I'm doing wrong.

 

[Joe4]

Hmmm...
And you copy and pasted my code exactly as-is?
That would seem to suggest that M2 is really not empty, but has something in it.
If M2 is truly empty, it should return FALSE.
Where did this data come from? Was it downloaded or copied from another non-Excel source (like the Web or another program)?

 

[Michael515]

Yes I did.

The worksheet I'm entering the formula into and the one I'm matching against pulls data from a database, but that shouldn't affect whether or not a Match occurs correct? After I enter the formula into the cell and fill down, I copy and paste over values, which in turn should just have numerical values in the cell. Or would issues still be caused either way?

 

[Joe4]

After I enter the formula into the cell and fill down, I copy and paste over values, which in turn should just have numerical values in the cell.

It is necessary to replace the formulas with hard-code values?
Usually you only have to do it if your are going to lose access to the underlying data (so the formulas won't work).

What is that formula?
Maybe it can be amended to return 0 instead of blanks.
It might be that a blank caused by a formula is not treated the same as a regular blank (where an entry never existed).

 

[Michael515]

I tried to replace the formula with hard code values to see if that would help with the problem I was encountering.

The formula in the cell is:=IFERROR(MATCH(K2, Sheet2'!$H$2:$H$1048576, 0), "")

 

[Joe4]

What if you tried the following instead:
=IFERROR(MATCH(K2, Sheet2'!$H$2:$H$1048576, 0), 0)

 

[Michael515]

THAT WORKED!!! Thank you so much :D

I guess VBA needs to literally see a zero for the comparison, as you alluded to, instead of a blank. Good Call. Again I appreciate all the help.

 

[Joe4]

You are welcome!

I guess VBA needs to literally see a zero for the comparison, as you alluded to, instead of a blank.

Usually (typically) it doesn't. But because I think it was copied and pasted from a formula, the cell really isn't really blank/empty, though it appears to be.