VBA Loop Not Working Properly

Michael515

Board Regular
Joined
Jul 10, 2014
Messages
136
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!

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

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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?
 
Upvote 0
Yes, I just ran a simple msgbox code to check, and it gave me the appropriate final value of 25.

Code:
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
 
Upvote 0
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.
 
Upvote 0
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:

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
 
Upvote 0
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.
Code:
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.
 
Upvote 0
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?
 
Upvote 0
Trying running this macro and let me know what it returns:

Code:
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?
 
Upvote 0
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.
 
Upvote 0
Try this and see what it returns:

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

Forum statistics

Threads
1,224,823
Messages
6,181,183
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top