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

 

[Joe4]

Did you check the value of LastRow to make sure it is picking up all your rows?
Are you sure that column A is a good column to look at in determining where the last row actually is?

 

[Michael515]

Yes, I just ran a simple msgbox code to check, and it gave me the appropriate final value of 25.

Sub Lastrow()
    Sheets("Sheet1").Select
    
    'Find last row in a dataset
    Dim Lastrow As Long
    Lastrow = Cells(Rows.Count, "A").End(xlUp).row
    
    MsgBox Lastrow
    
    
End Sub

 

[Joe4]

Did you try stepping through your code to see where things appear to be going wrong?
Note as you are stepping through your code, you can hover over the variables to see what their value is at that point in time.

 

[Michael515]

Ah I didn't know about the hover ability, thanks that's really helpful.

I actually noticed that the IF Then Else should have just been IF Then (no Else) which was causing the issue, however a new problem arises: If the cell value is Blank, the If still reads it as true, e.g. if M2 = 0 then for some reason it still triggers the then part of the function and just doesn't go to the next i, any thoughts on why that is happening?

Fixed Code:

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
        
            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

 

[Joe4]

Try temporarily amending your code to see what it thinks the value is, and what route it is taking in your code, i.e. add the following two lines under your loop, and replace the current IF line with the new one.

For i = 2 to LastRow
[COLOR=#ff0000]        myVal = Sheets("Sheet1").Range("M" & i).Value[/COLOR]
[COLOR=#ff0000]        MsgBox "Value of i: " & i & vbCrLf & "Value of Mi: " & myVal[/COLOR]
        If [COLOR=#ff0000]myVal[/COLOR] > 0 Then

This will return a message box telling you what the value is for each iteration of the loop.
See if the value really is zero, and if it is, does it perform the steps inside the IF ... THEN block.

 

[Michael515]

So the Msg Box reads:

Value of i: 2
Value of Mi:

(so Mi reads blank which is correct)

But even though blank it still triggers the THEN block ... Any thoughts?

 

[Joe4]

Trying running this macro and let me know what it returns:

Sub Test()
    MsgBox Len(Sheets("Sheet1").Range("M2"))
End Sub

Besides blanks, what exactly is in column M?
Numbers or Text, or a combination of the two?

 

[Michael515]

The macro returns: 0

The number format is set to general if that helps. The cells are blank, but as with your macro, the values are not 0.

I tried setting the code as <> 0 but the same problem persists :/

So besides blanks, some cells with have values greater than zero, but other than numbers nothing else.

 

[Joe4]

Try this and see what it returns:

Sub Test()
    MsgBox Round(Sheets("Sheet1").Range("M2").Value + 0,2) > 0
End Sub