For loops in a row not being carried out

Question

This code is copying and pasting column by column from "Daily Cash Position" file to "Bank Rec" file contiguously to the previous records. There are two For loops at the end of this code; the first headed by company code copy and paste, and the second headed by amount copy and paste. The code works fine up to the first For loop, but the second For loop never gets carried out. Can anyone please help?

Sub Geek_Squad_Project()

    Dim FilPicker As FileDialog 
    Dim CashPosition As String
    Dim BankRec As String
    Dim CP As String
    Dim BR As Excel.Workbook
    Dim i As Integer
    Dim c As Integer
    Dim b As Integer
    Dim x As Integer
    Dim d As Integer

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

    'assign path with filename in string format to CashPosition and file name in string format to CP

    CashPosition = Application.ActiveWorkbook.FullName
    CP = Application.ActiveWorkbook.Name 

    Set FilPicker = Application.FileDialog(msoFileDialogFilePicker) 

    With FilPicker
        .Title = "Select a daily cash position file"
        .AllowMultiSelect = False
            If .Show <> -1 Then
                GoTo ResetSettings
            '.Show = -1 means the user pressed the action button VS .Show = 0 means the user pressed the cancel button
        'selected file's full path with file name is stored as BankRec
            End If   
        BankRec = .SelectedItems(1)   
    End With

    Set BR = Workbooks.Open(Filename:=BankRec)

    BR.Worksheets("Bank Rec Master").Activate

    'clear filter from Bank Rec

    'If ActiveSheet.AutoFilterMode Then ActiveSheet.ShowAllData  

        If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
            ActiveSheet.ShowAllData
        End If  

    Workbooks(CP).Activate
    Worksheets("Daily Cash Position").Select  

    'save the last row number into c

    c = Cells(Rows.Count, 9).End(xlUp).Row  

    'company code copy and paste

    For i = 1 To c
        If Cells(i, 9) <> "" Then
            Cells(i, 9).Select
            x = Range(Cells(i, 9), Cells(i, 9).End(xlDown)).Count
            Range(Cells(i, 9), Cells(i, 9).End(xlDown)).Copy
            BR.Worksheets("Bank Rec Master").Activate
            b = Cells(Rows.Count, 1).End(xlUp).Row
            Cells(b + 1, 1).PasteSpecial Paste:=xlPasteValues
            i = i + x
        Else
        End If
    Next i

    'amount copy and paste

    For i = 1 To c
        If Cells(i, 10) = "R" Then
            Cells(i, 6).Copy 
            BR.Worksheets("Bank Rec Master").Activate
            d = Cells(Rows.Count, 2).End(xlUp).Row
            Cells(d + 1, 2).PasteSpecial Paste:=xlPasteValues
        ElseIf Cells(i, 10) = "D" Then
            Cells(i, 6).Copy
            BR.Worksheets("Bank Rec Master").Activate
            d = Cells(Rows.Count, 2).End(xlUp).Row
            Cells(d + 1, 2).PasteSpecial Paste:=xlPasteFormulasAndNumberFormats
            Cells(d + 1, 2).Value = Cells(d + 1, 2).Value * -1     
        End If
    Next i

    ResetSettings:

    'in case user clicks Cancel 

    Application.ScreenUpdating = True

    Application.EnableEvents = True

    Application.DisplayAlerts = True 

End Sub

Your VBA experience will be much improved if you follow the guidelines laid out here: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba If your code depend on a certain sheet being active when it runs then pretty soon you'll find it doesn't work as you expect, and debugging that is not fun. If you always specify a worksheet when calling (eg) `Range()` or `Cells()` then things are going to be a lot more predictable. — Tim Williams, Oct 20 '19 at 15:54
@TimWilliams I read the page and got the code worked out. It was a great help, thanks! — Bonnie J, Oct 22 '19 at 02:31

score 0 · Answer 1 · answered Oct 22 '19 at 02:50

Sub Geek_Squad_ProjectII()

Dim FilPicker As FileDialog
Dim CashPosition As String
Dim BankRec As String
Dim CP As Excel.Workbook
Dim BR As Excel.Workbook
Dim i As Integer
Dim c As Integer
Dim b As Integer
Dim x As Integer
Dim d As Integer

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual


'assign path with filename in string format to CashPosition and file name in string format to CP


CashPosition = Application.ActiveWorkbook.FullName

Set CP = ActiveWorkbook
Set FilPicker = Application.FileDialog(msoFileDialogFilePicker)

With FilPicker
.Title = "Select a bank rec file"
.AllowMultiSelect = False
    If .Show <> -1 Then
    GoTo ResetSettings

    '.Show = -1 means user pressed the action button VS .Show = 0 means user pressed the cancel button

    'selected file's full path with file name is stored as BankRec

    End If


    BankRec = .SelectedItems(1)

End With


Set BR = Workbooks.Open(Filename:=BankRec)

BR.Worksheets("Bank Rec Master").Activate


'clear filter from Bank Rec


If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then


ActiveSheet.ShowAllData



End If


CP.Activate

Worksheets("Daily Cash Position").Select


'save the last row number into c



c = CP.Worksheets("Daily Cash Position").Cells(Rows.Count, 9).End(xlUp).Row


'company code copy and paste
For i = 1 To c
If IsNumeric(CP.Worksheets("Daily Cash Position").Cells(i, 9)) = True Then
CP.Worksheets("Daily Cash Position").Cells(i, 9).Copy
b = BR.Worksheets("Bank Rec Master").Cells(Rows.Count, 1).End(xlUp).Row
BR.Worksheets("Bank Rec Master").Cells(b + 1, 1).PasteSpecial Paste:=xlPasteValues
Else
End If
Next i



'amount copy and paste
For i = 1 To c
If CP.Worksheets("Daily Cash Position").Cells(i, 10) = "R" Then
CP.Worksheets("Daily Cash Position").Cells(i, 6).Copy
d = BR.Worksheets("Bank Rec Master").Cells(Rows.Count, 9).End(xlUp).Row
BR.Worksheets("Bank Rec Master").Cells(d + 1, 9).PasteSpecial Paste:=xlPasteValues
ElseIf CP.Worksheets("Daily Cash Position").Cells(i, 10) = "D" Then
CP.Worksheets("Daily Cash Position").Cells(i, 6).Copy
d = BR.Worksheets("Bank Rec Master").Cells(Rows.Count, 9).End(xlUp).Row

BR.Worksheets("Bank Rec Master").Cells(d + 1, 9).PasteSpecial Paste:=xlPasteFormulasAndNumberFormats

BR.Worksheets("Bank Rec Master").Cells(d + 1, 9).Value = BR.Worksheets("Bank Rec Master").Cells(d + 1, 9).Value * -1

End If

Next i

ResetSettings:

'in case user clicks Cancel

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True

End Sub

For loops in a row not being carried out

1 Answers1