Using IsEmpty to stop loop

excel, vba

Solution

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

Problem

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.

Original source

Related problems