VBA Excel add value and formula beyond the last row created by the loop

Question

I want to populate the total amount summary after the last row generated by the loop, as per below:

where all the rows are based on the amount of the cell located at the different sheet ("Frontsheet").

The initial code for the loop with copy looks as follows:

   Dim i As Long

   Dim rg As Range, rg2 As Range
   Dim ws1 As Worksheet, ws2 As Worksheet

   Set ws1 = ThisWorkbook.Sheets("Frontsheet")
   Set ws2 = ThisWorkbook.Sheets("Fibre drop release sheet")
   Set rg = ws1.Range("D32")
   Set rg2 = ws2.Range("B3:N3")

   For i = 1 To rg

   rg2.Copy Destination:=ws2.Range("B4").Resize(RowSize:=rg.Value)

   Next i

The code, which I built including the option which I want looks as follows:

Sub Address()

   Dim i As Long, LastRow As Long

   Dim rg As Range, rg2 As Range, rg3 As Range, rg4 As Range
   Dim ws1 As Worksheet, ws2 As Worksheet

   Set ws1 = ThisWorkbook.Sheets("Frontsheet")
   Set ws2 = ThisWorkbook.Sheets("Fibre drop release sheet")
   Set rg = ws1.Range("D32")
   Set rg2 = ws2.Range("B3:N3")
   Set rg3 = Cells("I" & .Rows.Count).End(xlUp).Row
   Set rg4 = Cells("J" & .Rows.Count).End(xlUp).Row
   Set LastRow = Cells("J" & .Rows.Count).End(xlUp).Row - 1

   For i = 1 To rg

   rg2.Copy Destination:=ws2.Range("B4").Resize(RowSize:=rg.Value)
   Next i

   rg3.Value = "Total"   
   rg4.Value = "=SUM(J3: & Lastrow)"
   End Sub

It was based on the hints here:

counting a range (rows) begingin in B3

Better way to find last used row

Excel VBA: Loop InputBox and add values to cell range

in order to find the last row.

Unfortunately I am getting an error: Invalid or unqualified reference where the debugger points the following line:

    Set rg3 = Cells("I" & .Rows.Count).End(xlUp).Row,   especially .Rows

As per the explanation above I should have the With statement https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/invalid-or-unqualified-reference

But it looks like it complicates my situation because I set it as the range.

How can I add some stuff beyond the last row, which was populated by the loop?

score 2 · Accepted Answer · answered Dec 04 '20 at 14:02

Here is my approach that will get you started. This code is open to improvements.

Some general suggestions:

Name your procedures and your variables to something meaninful
Indent your code
Organize your logic in steps, this will give you clarity and help you if you need to debug it

About your code:

Defining the variables in this way can lead to errors

Dim i As Long, LastRow As Long

Dim rg As Range, rg2 As Range, rg3 As Range, rg4 As Range Dim ws1 As Worksheet, ws2 As Worksheet

With these two, you're setting a range to a number (row)

Set rg3 = Cells("I" & .Rows.Count).End(xlUp).Row Set rg4 = Cells("J" & .Rows.Count).End(xlUp).Row

This ain't the last row (is the previous one) so either the variable name or the formula are wrong

Set LastRow = Cells("J" & .Rows.Count).End(xlUp).Row - 1

In here you're looping (or at least trying to), but you're not doing anything inside the loop because you're already defining the range size you want to copy

For i = 1 To rg

rg2.Copy Destination:=ws2.Range("B4").Resize(RowSize:=rg.Value)
Next i

Code:

Public Sub CopyInitialRow()
    
    ' Get rows' size
    Dim frontRowSize As Long
    frontRowSize = ThisWorkbook.Sheets("Frontsheet").Range("D32").Value
    
    ' Set the release sheet
    Dim releaseSheet As Worksheet
    Set releaseSheet = ThisWorkbook.Sheets("Fibre drop release sheet")
    
    ' Set the source range
    Dim sourceRange As Range
    Set sourceRange = releaseSheet.Range("B3:N3")
    
    ' Set the target range
    Dim targetRange As Range
    Set targetRange = releaseSheet.Range("B4").Resize(rowSize:=frontRowSize)
    
    ' Copy source to target range
    sourceRange.Copy Destination:=targetRange
    
    ' Get the last row on target sheet
    Dim lastRow As Long
    lastRow = releaseSheet.Cells(releaseSheet.Rows.Count, "J").End(xlUp).Row
    
    ' Write total label and value
    releaseSheet.Range("I" & lastRow + 1).Value = "Total"
    releaseSheet.Range("J" & lastRow + 1).Formula = "=SUM(J3:J" & lastRow & ")"
    
End Sub

Hope this helps. Let me know if it works.

It works perfectly! Thannk you. If I would like to make a border around it, is it enough to type: With releaseSheet.Range("I" & lastRow + 1) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 3 End With — Geographos, Dec 04 '20 at 15:46
Pls give a try and post another question if you need any help — Ricardo Diaz, Dec 05 '20 at 22:55

VBA Excel add value and formula beyond the last row created by the loop

1 Answers1