Search code examples
excelvbaloops

VBA Loop does not loop

I am working on a loop to look through the export file sheet column AF, which is column 32; if a cell in column 32 equals #N/A, then copy the value in column AE, which is column 31, and paste in sheet 'errors and resolutions' column A last row. My loop breaks at the next loop on the if statement line.

    'Loop through column AF
    For i = 2 To last row
    
    'Activate worksheet
    Worksheets("Exported File").Activate
    
    'If condition is met then copy value from column AE
        If ws.Cells(i, 32).Value = CVErr(xlErrNA) Then ws.Cells(i, 31).Copy Else 'do nothing
    '
            'Activate Errors & Resolutions sheet
            Worksheets("Errors & Resolutions").Activate
            
            'Find next empty cell
            Range("A1").End(xlDown).Offset(1).Select

           'Paste values123 (Actual values)
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone

'
        End If
'
    Next i
Solution

  • Copy Matching Values

    For...Next

    Sub CopyValuesFor()

    ' Workbook
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    ' Source
    Dim sws As Worksheet: Set sws = wb.Sheets("Export File")
    Dim slRow As Long: slRow = sws.Cells(sws.Rows.Count, "AF").End(xlUp).Row
    
    ' Destination
    Dim dws As Worksheet: Set dws = wb.Sheets("Errors & Resolutions")
    Dim dcell As Range: Set dcell = dws.Cells(dws.Rows.Count, "A").End(xlUp)

    ' Declare additional variables.
    Dim sValue As Variant, i As Long
 
    ' Loop through the rows of the source sheet.
    For i = 2 To slRow
        sValue = sws.Cells(i, "AF").Value
        ' Comparing an error value with a non-error value will result
        ' in a type mismatch error. Therefore make sure you compare
        ' only errors with errors:
        If IsError(sValue) Then ' the value in column 'AF' is an error
            ' Only if the error is a Not-Available error,...
            If sValue = CVErr(xlErrNA) Then
                ' ... reference the next destination cell...
                Set dcell = dcell.Offset(1)
                ' ... and write the corresponding value from column 'AE' to it.
                dcell.Value = sws.Cells(i, "AE").Value
            End If
        End If
    Next i
    
    ' Inform.
    MsgBox "Values copied.", vbInformation

End Sub

    enter image description here

    For Each...Next

    Sub CopyValuesForEach()

    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim sws As Worksheet: Set sws = wb.Sheets("Export File")
    
    Dim slrg As Range:
    Set slrg = sws.Range("AF2", sws.Cells(sws.Rows.Count, "AF").End(xlUp))
    Dim srrg As Range: Set srrg = slrg.EntireRow.Columns("AE")
    
    Dim dws As Worksheet: Set dws = wb.Sheets("Errors & Resolutions")
    Dim dcell As Range: Set dcell = dws.Cells(dws.Rows.Count, "A").End(xlUp)

    Dim scell As Range, sValue As Variant, sRow As Long, dRow As Long
 
    For Each scell In slrg.Cells
        sRow = sRow + 1
        sValue = scell.Value
        If IsError(sValue) Then ' is an error
            If sValue = CVErr(xlErrNA) Then ' is a #N/A error
                dRow = dRow + 1
                dcell.Offset(dRow).Value = srrg.Cells(sRow).Value
            End If
        End If
    Next scell

    MsgBox "Out of " & sRow & " cell" & IIf(sRow = 1, "", "s") & " in ""'" _
        & sws.Name & "'!" & srrg.Address(0, 0) & """, " & dRow _
        & " had ""#N/A"" in the corresponding cell" _
        & IIf(dRow = 1, "", "s") & " of ""'" _
        & sws.Name & "'!" & slrg.Address(0, 0) & """ and " _
        & IIf(dRow = 1, "was", "were") & " copied to ""'" _
        & dws.Name & "'!" & dcell.Offset(1).Resize(dRow).Address(0, 0) _
        & """.", vbInformation

End Sub