Using IsEmpty to stop loop

Question

I found a thread that applied directly to the code that I'm trying to build here Excel VBA: Loop through cells and copy values to another workbook.

Sub test()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim CurCell_1 As Range, CurCell_2 As Range
Dim Ran As Range
Dim Group As Range, Mat As Range

Application.ScreenUpdating = True

Set ws1 = ActiveWorkbook.Sheets("Scrap")
Set ws2 = ActiveWorkbook.Sheets("FC Detail")

For Each Mat In ws1.Range("E:E")
    Set CurCell_2 = ws2.Range("F8")
    For Each Group In ws1.Range("E:E")
        Set CurCell_1 = ws1.Cells(Group.Row, Mat.Column)
        If Not IsEmpty(CurCell_2) Then
            CurCell_2.Value = CurCell_1.Value
        End If
    Next
Next

End Sub

This code works with one exception, it loops continually.

I thought that If Not IsEmpty would be the descriptor to VBA that once it reaches the end of the list to cease the program.

What do you mean by "loops continually?" How do you know the code "works" if it never exits the loop? — Robert Harvey
– Robert Harvey, Commented Jul 10, 2012 at 22:39
It is running continuously because you are addressing the entire column... twice. Why not find the last row and then limit your range as shown in the other link? — Siddharth Rout
– Siddharth Rout, Commented Jul 10, 2012 at 23:13

Siddharth Rout · Accepted Answer · 2012-07-10 23:21:18Z

5

Further to my comment, try this. This will be much faster

Sub Test()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim CurCell_1 As Range, CurCell_2 As Range
    Dim Group As Range, Mat As Range, Ran As Range
    Dim lRow As Long

    Set ws1 = ActiveWorkbook.Sheets("Scrap")
    Set ws2 = ActiveWorkbook.Sheets("FC Detail")

    With ws1
        lRow = .Range("E" & .Rows.Count).End(xlUp).Row

        Set Ran = .Range("E1:E" & lRow)

        For Each Mat In Ran
            Set CurCell_2 = ws2.Range("F8")
            For Each Group In Ran
                Set CurCell_1 = .Cells(Group.Row, Mat.Column)
                If Not IsEmpty(CurCell_2) Then
                    CurCell_2.Value = CurCell_1.Value
                End If
            Next
        Next
    End With
End Sub

answered Jul 10, 2012 at 23:21

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jon Crowell Over a year ago

+1. Colin, if you do any Excel VBA development at all, finding the last row is something you'll do all the time. The way Siddharth finds it in the code above is something you should memorize. You'll also want to get in the habit of stepping through your code (F8) to see what it is actually doing.

ForEachLoop Over a year ago

Speaking of good habits, put DoEvents in all loops, especially during development. That way you can always break in, in the case of an endless loop.

Colin Sinclair Over a year ago

Thanks very much for all of your help, with a few modifications this worked out great.

Collectives™ on Stack Overflow

Using IsEmpty to stop loop

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related