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

 

[artz]

I didn't notice originally, but it appears along the way you changed your loop from:

For i = 1 to Range("AD2")

to

For r = 1 to Range("AD2")

So you appear to be trying to use "r" for two entirely different things;
- your loop counter
- your last row finder

Don't do that. That may be what is causing the issue. Continue to use "i" as your loop counter.

Joe4,

I think that I changed from i to r to test the row counter but didn't change it back to i. I just changed the r to i and the code still doesn't fill AE, AF and AG:

Sub Test2()

Dim i As Integer

For i = 1 To 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)

r = Range("AG" & Rows.Count).End(xlUp).Offset(1, 0).Row

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

Next i

End Sub

I deleted any data that may have gotten into this range. (Ctrl+Shft down arrow key delete). This should be simple- what may be going on?

Thanks,

Art

 

[artz]

Joe4,

Got it! So a couple of questions:

Both of these compile:

i To AD2

and

1 To Range("AD2")

However, the

i To AD2

doesn't work. What's the difference?

Next question is why does the VBA code take so long run? If I run random number generator with cell formulas, the cells are filled as soon as I hit enter.

That code is:

=randbetween(1,6)

. The next column adds the two adjacent columns with the randbetween calculation.

Do you have an idea what's going on?

Thanks,

Art

 

[Joe4]

However, the

i To AD2

doesn't work. What's the difference?

If you want to refer to the value in cell AD2, you need to use Range("AD2").
If you try to use AD2, VBA see that as a variable named AD2. If you have not assigned any value to a variable named AD2, then its value will be zero.

Next question is why does the VBA code take so long run? If I run random number generator with cell formulas, the cells are filled as soon as I hit enter.

That code is:

=randbetween(1,6)

. The next column adds the two adjacent columns with the randbetween calculation.

Do you have an idea what's going on?

What value are you setting for AD2?
How long exactly is it taking to run?
Do you have any other VBA code, like Worksheet_Change event procedure code, that may be automatically called and running by doing all these updates?

I think we can rewrite to run faster. Try this:

Sub Test2()

Dim i As Integer
Dim r As Long

Application.ScreenUpdating = False

'Find last row in column AE with data
r = Range("AE" & Rows.Count).End(xlUp).Row

For i = 1 To Range("AD2").Value
    Cells(r + i, "AE").Value = Int(Rnd * 6 + 1)
    Cells(r + i, "AF").Value = Int(Rnd * 6 + 1)
    Cells(r + i, "AG").Formula = "=AE" & r + i & "+AF" & r + i
Next i

Application.ScreenUpdating = True

End Sub

 

[artz]

Joe4,

Seems that last addition got the code running better, thanks. This morning, I rebuilt the entire worksheet from scratch; it's a lot cleaner now. Here's a shot of the dashboard.

Thanks,

Art