Using VBA to take sum and place in next unused row in column

[artz]

Hi,

I am using the following VBA code to simulate the rolling of dice. The code puts the random numbers in the next unused row in columns AE and AF respectively.

Sub Random1()

Dim i As Integer

For i = 1 To Range("AD2")

Range("AE" & Rows.Count).End(xlUp).Offset(1, 0).Formula = Int(Rnd * 6 + 1)
Range("AF" & Rows.Count).End(xlUp).Offset(1, 0).Formula = Int(Rnd * 6 + 1)

Next i

End Sub

I would to use VBA to take the sum of each pair of values in columns AE and AF that are in the same row. The would be placed in the next unused row in column AG. I tried modifying the code used in the Random sub to do this, but received an error. The code I used is:

Sub Sum()

Range("AG" & Rows.Count).End(xlUp).Offset(1, 0).Formula = Range("AE") + Range("AF")

End Sub

Does anyone have a suggestion to fix my errant code?

Thanks,

Art

 

[Joe4]

A few things.

Never used reserved words like "Sum" as the name of procedures, functions, or variables. Doing so can cause errors and unexpected results ("Sum" is already an existing Excel function).
In your code, you are not creating a formula, and you cannot have a range address with no row reference (i.e. Range("AE") and Range("AF") are missing row references).

I am not sure if you are just trying to sum up columns AE and AF for that row, or for the entire column.
For that particular row, the formula could look something like this.

Sub MySum()

    Range("AG" & Rows.Count).End(xlUp).Offset(1, 0).FormulaR1C1 = "=RC[-1]+RC[-2]"

End Sub

 

[artz]

Hi Joe4,

Thanks for your response and code. I didn't know that Sum was a reserved word; thanks for the tip. The number of dice rolls is set by the value in AD2. Each click of the Command Button produces new roll values which are concatenated to the existing roll values in columns AE and AF. These new roll values would be summed and concatenated to the existing sum values in column AG.

When I ran your code, nothing appeared to happen. The sum values did not show up in column AG.

Am I missing something?

Thanks,

Art

 

[Joe4]

Basically, "reserved" words are names of existing Excel functions, methods, properties, etc. (there is a native Excel function named "Sum").

I am not sure what exactly how you want your expected results to look (whether or not you want a sum for each row, or just one total sum at the bottom).
Can you post a small data sample, along with your expected results in column AG?

 

[artz]

Hi,

I thought that I was clear, but I guess not. Here's a screen cap of what I'm looking for terms of the sums. A nice feature if possible, would be to have a choice to either clear the contents of AE, AF, and AG starting at row two or to concatenate each new set of rolls. Can this feature be added easily? For instance a MsgBox that says, "Clear Contents?" Just a thought. Let's get the sums done first.

Thanks,

Art

 

[Joe4]

You can do the Sum in the same sub procedure as your original one, i.e.

Sub Random1()

    Dim i As Integer

    For i = 1 To Range("AD2")
        Range("AE" & Rows.Count).End(xlUp).Offset(1, 0).Formula = Int(Rnd * 6 + 1)
        Range("AF" & Rows.Count).End(xlUp).Offset(1, 0).Formula = Int(Rnd * 6 + 1)
        Range("AG" & Rows.Count).End(xlUp).Offset(1, 0).FormulaR1C1 = "=RC[-1]+RC[-2]"
    Next i

End Sub

 

[artz]

You can do the Sum in the same sub procedure as your original one, i.e.

Sub Random1()

    Dim i As Integer

    For i = 1 To Range("AD2")
        Range("AE" & Rows.Count).End(xlUp).Offset(1, 0).Formula = Int(Rnd * 6 + 1)
        Range("AF" & Rows.Count).End(xlUp).Offset(1, 0).Formula = Int(Rnd * 6 + 1)
        Range("AG" & Rows.Count).End(xlUp).Offset(1, 0).FormulaR1C1 = "=RC[-1]+RC[-2]"
    Next i

End Sub

Hi Joe4,

Same issue as before you combined the subs: nothing is written in column AG.

Art

 

[Joe4]

Clear your range, and start over (so that you have titles in AD1 to AG1. but no data below it.

Then if you run the code, you should see something like this:

BEFORE IMAGE:

AFTER ENTERING 10 IN CELL AD2 AND RUNNING VBA CODE IMAGE:

This is what I got when I ran the code I posted in my previous post.
If it is not working for you, make sure you copied and pasted it correctly.

 

[artz]

Clear your range, and start over (so that you have titles in AD1 to AG1. but no data below it.

Then if you run the code, you should see something like this:

BEFORE IMAGE:
View attachment 68842

AFTER ENTERING 10 IN CELL AD2 AND RUNNING VBA CODE IMAGE:
View attachment 68843

This is what I got when I ran the code I posted in my previous post.
If it is not working for you, make sure you copied and pasted it correctly.

This should be simple. Here's the code as copied and pasted and the same result; nothing in AG???? Am running XL 2016 on Win 10.

 

[Joe4]

This should be simple. Here's the code as copied and pasted and the same result; nothing in AG???? Am running XL 2016 on Win 10.

That does not seem possible, unless you already have something in column AG much further down the page.

If you run this code, what does it return?

Sub Test()
    
    Dim lr As Long
    
    If Cells(Rows.Count, "AG") <> "" Then
        lr = Rows.Count
    Else
        lr = Cells(Rows.Count, "AG").End(xlUp).Row
    End If
    
    MsgBox "The last row in column AG with an entry is row: " & lr
    
End Sub