syntax of for...next loop

[dpaton05]

Is there anything wrong with the syntax of my for next loop?

Sub cmdCopyr()
Application.ScreenUpdating = False

'declare variables
Dim TableLastRow As Long
Dim LastRow As Long
Dim TableNumberRows As Long
Dim iCounter As Long                                    'counter of for..next loop
Dim tblRows As Integer                                  'number of rows in tblCosting
Dim tblrow As ListRow                                   'specific row in tblCosting
Dim Combo As String                                     'Combo worksheet name
Dim sht As Worksheet
Dim Start As Long                                       'number of first row in tblCosting
Dim Costing As String

'assign values to variables
Set sht = Worksheets("home")

    With sht


        TableLastRow = Split(Sheets("Home").ListObjects("tblCosting").DataBodyRange.Address, "$")(4)            'last row in tblCosting of home sheet
        Combo = Worksheets("Home").Range("Y5")                                                                  'concaternation of cells to get name of worksheet to put data
        LastRow = Worksheets(Combo).Cells(Rows.Count, "A").End(xlUp).Row + 1                                    'number of first empty row in column A of Combo
        Costing = Worksheets("home").ListObjects("tblCosting")
        
'        TableNumberRows = Rows("tblCosting")                                          'Worksheets("home").ListObjects("tblCosting").Rows.Count
        Start = "5"
        
         
        For iCounter = Start To TableLastRow Step 1
            .ListObjects("tblCosting").Range.Cells(iCounter, 1).Resize(, 10).copy
            With Worksheets(Combo).Cells(LastRow, iCounter)
                .PasteSpecial Paste:=xlPasteValues
                .Columns("A").NumberFormat = "dd/mm/yyyy"
            End With
            .ListObjects("tblCosting").Range.Cells(iCounter, 1).copy
                Worksheets(Combo).Cells(LastRow, 11).PasteSpecial Paste:=xlPasteValues
            .ListObjects("tblCosting").Range.Cells(iCounter, 30).Resize(, 32).copy
                Worksheets(Combo).Cells(LastRow, 14).PasteSpecial Paste:=xlPasteValues
        Next iCounter
        
                'Check if activities is selected in column I
                If Worksheets("home").Range("I5") = "Activities" Then
                    Worksheets(Combo).Range("K" & LastRow).Value = Worksheets("home").Range("AC" & LastRow)          'copy formula to total column if activities are selected
                    
                    'Worksheets(Combo).Range("I" & Lastrow).ClearContents                                            'clear gst component if activities are selected
                Else
                    Worksheets(Combo).Range("L" & LastRow).Formula = "=K" & LastRow & "*.1"                       'if activities are not selected, GST formula will be added in to column H
                    Worksheets(Combo).Range("M" & LastRow).Formula = "=L" & LastRow & "+K" & LastRow               'if activities are not selected, add formula in to column I that will sum columns G + H
                End If
                
            Worksheets(Combo).Columns("H:J").NumberFormat = "$#,##0.00"                                             'format values in columns G to I of Combo
            Call SortDates                                                                                          'format cells to be in ascending date order
    End With
    Application.CutCopyMode = False                                                                         'cancel Cut or Copy mode

    
Application.ScreenUpdating = True
End Sub

Thanks

 

[Kenneth Hobson]

  Start = 5

 

[RoryA]

Why do you ask? Do you have a problem with it?

 

[dpaton05]

Why do you ask? Do you have a problem with it?

I do have a problem with it Rory. I am still learning to code in VBA so that is why I need all this help. My code does nothing when I run it, so I thought I would ask for help but do it bit by bit so that none of it gets left out. All I want it to do is loop through the rows in tblCosting and place them in the appropriate sheet depending on the day, for instance, the workbook has the sheets of "July 2018" to "June 2019". There may be rows that have dates in different months so that's why it needs to go through the rows individually, checking the date of each one but I don't know how to do it.

I think I have encountered errors like this before. I think there is just one or two lines that are incorrect and that is throwing all kinds of random little errors.

This is the code I have tried to write:

Sub cmdCopyr()
Application.ScreenUpdating = False

'declare variables
Dim TableLastRow As Long
Dim LastRow As Long
Dim TableNumberRows As Long
Dim iCounter As Long                                    'counter of for..next loop
Dim tblRows As Integer                                  'number of rows in tblCosting
Dim tblrow As ListRow                                   'specific row in tblCosting
Dim Combo As String                                     'Combo worksheet name
Dim sht As Worksheet
Dim Start As Long                                       'number of first row in tblCosting
Dim Costing As String

'assign values to variables
Set sht = Worksheets("home")

    With sht


        TableLastRow = Split(Sheets("Home").ListObjects("tblCosting").DataBodyRange.Address, "$")(4)            'last row in tblCosting of home sheet
        Combo = Worksheets("Home").Range("Y5")                                                                  'concaternation of cells to get name of worksheet to put data
        LastRow = Worksheets(Combo).Cells(Rows.Count, "A").End(xlUp).Row + 1                                    'number of first empty row in column A of Combo
        Costing = Worksheets("home").ListObjects("tblCosting")
        
'        TableNumberRows = Rows("tblCosting")                                          'Worksheets("home").ListObjects("tblCosting").Rows.Count
        Start = "5"
        
         
        For iCounter = Start To TableLastRow Step 1
            .ListObjects("tblCosting").Range.Cells(iCounter, 1).Resize(, 10).copy
            With Worksheets(Combo).Cells(LastRow, iCounter)
                .PasteSpecial Paste:=xlPasteValues
                .Columns("A").NumberFormat = "dd/mm/yyyy"
            End With
            .ListObjects("tblCosting").Range.Cells(iCounter, 1).copy
                Worksheets(Combo).Cells(LastRow, 11).PasteSpecial Paste:=xlPasteValues
            .ListObjects("tblCosting").Range.Cells(iCounter, 30).Resize(, 32).copy
                Worksheets(Combo).Cells(LastRow, 14).PasteSpecial Paste:=xlPasteValues
        Next iCounter
        
                'Check if activities is selected in column I
                If Worksheets("home").Range("I5") = "Activities" Then
                    Worksheets(Combo).Range("K" & LastRow).Value = Worksheets("home").Range("AC" & LastRow)          'copy formula to total column if activities are selected
                    
                    'Worksheets(Combo).Range("I" & Lastrow).ClearContents                                            'clear gst component if activities are selected
                Else
                    Worksheets(Combo).Range("L" & LastRow).Formula = "=K" & LastRow & "*.1"                       'if activities are not selected, GST formula will be added in to column H
                    Worksheets(Combo).Range("M" & LastRow).Formula = "=L" & LastRow & "+K" & LastRow               'if activities are not selected, add formula in to column I that will sum columns G + H
                End If
                
            Worksheets(Combo).Columns("H:J").NumberFormat = "$#,##0.00"                                             'format values in columns G to I of Combo
            Call SortDates                                                                                          'format cells to be in ascending date order
    End With
    Application.CutCopyMode = False                                                                         'cancel Cut or Copy mode

    
Application.ScreenUpdating = True
End Sub

Sub SortDates()
'
' sortDates Macro
Worksheets("home").Unprotect Password:="costings"
  'Set up your variables and turn off screen updating.
   Dim iCounter As Integer
   Dim Combo As String
        Combo = Worksheets("Home").Range("Y5")
   Application.ScreenUpdating = False
   
  
   'Sort the rows based on the data in column C

'   Sheets(Combo).Sort Key1:=Range("A4"), _
      Order1:=xlAscending, Header:=xlYes
   
   'Clear out the temporary sorting value in column C, and turn screen updating back on.
   'Columns(3).ClearContents
   Application.ScreenUpdating = True
'Worksheets("home").Protect Password:="costings"
End Sub

Can someone help me with this code please?

 

[Norie]

If you want to loop through the table why not use For Each?

 

[dpaton05]

If you want to loop through the table why not use For Each?

Someone that has been helping me told me I should use for next loops. I can't quite remember the reason but it was something to do with using the counter as something else, not just a counter.

 

[dpaton05]

Can someone help me with this problem please?

 

[Norie]

What, in words, is the code meant to do?

Have you tried stepping through it with F8 to see what's happening/not happening?

 

[dpaton05]

What, in words, is the code meant to do?

Have you tried stepping through it with F8 to see what's happening/not happening?

The spreadsheet is used for calculating costings. Here is a list of things the spreadsheet needs to be able to do-

1. There is a table on the home tab called tblCosting. Once all of the data for a costing is entered, totals will be calculated.
2. The table has buttons to add or delete rows.
3. Once all the entries have been entered for a costing (this can be 1-x number of rows), I want to run the code in my previous message and have it copy every line in the table and place each line in the correct sheet depending on the date of the particular costing.
4. Not every column of tblCosting is needed to be copied to the monthly sheets. For each row, columns A-J need to be copied, O-Q need to be copied and AD-AF need to be copied.
5. All of those columns need to be pasted next to each other in that order on the monthly sheets.

I think that pretty much sums up the issues I have been struggling with. I don't think I have missed anything but let me know if there is something.

I have tried stepping through the code but it seems to get stuck in a loop of going through the code that calculates Easter so it won't allow me to step all the way through it.

Thanks,
Dave

 

[Norie]

Dave

How is it determined which sheet each row is to be copied to?