CODEWITHSUNDEEP
vbaexcelfor-loopif-statement
Eric Low
Eric Low

Reputation: 1

VBA FOR loop runs for two loops

I'm trying to copy and paste a list of names from one worksheet to a specific cells in another sheets, depends on the row # in the "List" sheet is even or odd.

However, the FOR loop only runs for two loops : ie. ONE loop for name in row # even and ONE loop for name in row # odd.

Can anyone tell me where is my mistake?

I took away the IF function, and everything works well. But i need the IF function to determine Odd/Even row number.

My current code as below:

Sub Update_Print()


Dim i As Integer

For i = 7 To 1000
    Sheets("List").Select
    If i Mod 2 > 0 Then

        Cells(i, 1).Select
        Selection.Copy
        Sheets("Receipts").Select
        Cells(i + 30, 4).Select
        ActiveSheet.Paste

     Else
        Cells(i, 1).Select
        Selection.Copy
        Sheets("Receipts").Select
        Cells(i + 30, 10).Select
        ActiveSheet.Paste

    ActiveSheet.Print

Exit For

End If


Next i



End Sub

Upvotes: 0

Views: 171

Answers (1)

Albert
Albert

Reputation: 38

the for loop and if..else loop are probably misplaced.

May I suggest the following instead, which also tries to avoid the use of copy-pasting which usually slows the execution time.

Sub Update_Print()

Dim i As Integer
Dim sht1, sht2 As Worksheet
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Set sht1 = ActiveWorkbook.Sheets("List")
Set sht2 = ActiveWorkbook.Sheets("Receipts")

sht1.Activate
For i = 7 To 1000

    If i Mod 2 > 0 Then
        sht1.Cells(i, 1) = sht2.Cells(i + 30, 4)
    Else
        sht1.Cells(i, 1) = sht2.Cells(i + 30, 10)
    End If

Next i
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
sht1.PrintOut

End Sub

Are you trying to print the sheet after the macro runs? I've noticed the placement of your ActiveSheet.Print might be in a rather dangerous position, as it would be printing your activesheet about 1000 times! I've allocated it outside of the loop instead after the complete execution of the For Loop.

This method would be faster for copying values, but it doesn't bring across any formatting.

Upvotes: 1

Related Questions