VBA: Second loop overwriting first loop

Question

Quick and, I assume, relatively easy question regarding VBA in Excel. I'm writing my first nested loop and I'm running into the problem where my second increment overwrites the first one. Here's the code:

Sub RandNumRang()
Dim i As Integer, Row As Integer, Col As Integer
Dim j As Integer
Dim LastRow As Long
Dim NoRows As Integer
Dim MinNum As Double, MaxNum As Double
Dim NumFormula As String
Col = 1 'Write random numbers on Column A
'Get the range and number of random values needed
For j = 3 To 4
    NoRows = ActiveSheet.Range("G" & j).Value 'No of random values needed
    MinNum = ActiveSheet.Range("E" & j).Value 'Min Range
    MaxNum = ActiveSheet.Range("F" & j).Value 'Max Range
    NumFormula = "=RANDBETWEEN(" & MinNum & "," & MaxNum & ")" 'Generate between Range
    i = 0
    For Row = 2 To NoRows + 1
    i = i + 1
        ActiveSheet.Cells(Row, Col).Value = i 'ID
        ActiveSheet.Cells(Row, Col + 1).Formula = NumFormula 'Random Value
        ActiveSheet.Cells(Row, Col + 2).FormulaR1C1 = "=TIME(0,0,RC[-1])" 'Convert to time
    Next Row
Next j
End Sub

I'm trying to generate a set of random time intervals. The code does exactly what I want it to do except my values are being overwritten. The j loop will eventually have more than 2 increments but this code will have the same overwriting problem regardless. I'm pretty sure it has something to do with me specifying

For Row=2 To NoRows + 1

as the starting point of the nested loop but again this being my first run at a VBA script I figured I'd reach out because every fix I've tried has failed. Thanks in advance, and let me know if you need any further details.

Also, I have attached a screenshot of the worksheet for reference:

enter image description here

In your example are you saying you want 30 random times based of min = 1 and max = 40 and then another 25 random times based off min = 41 and max = 80? So you should have 55 total ID values in column A? — Alex P
– Alex P, Commented Dec 31, 2013 at 16:10
Correct. Notice that currently the first 25 'IDs' have values between 41-80 and the last 5 have values between 1-40. This is where the overwriting occurs. — eroma934
– eroma934, Commented Dec 31, 2013 at 16:17

Andrew · Accepted Answer · 2013-12-31 16:11:29Z

4

So far as I can see it is performing as expected. You set:

 Col = 1

This then remains the same for both loops. Then you have:

For Row = 2 To NoRows + 1

So in your second loop you are always going to start at row 2, column 1 so it is always going to overwrite.

You either need to set Col to be 4 (or 5) in the second iteration or else keep a track of where you are in the rows and append to the end,

answered Dec 31, 2013 at 16:11

Andrew

2,3253 gold badges27 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

eroma934 Over a year ago

I'd like to append to the end because the number of iterations will be greater than 2 in the final output. I choose 2 iterations as an example to test it out. I was trying to do this earlier by looking for the first empty cell in column A after the first iteration, but I had no luck. Would you be able to provide the syntax for this?

eroma934 Over a year ago

Too quick! Thanks for looking into this.

Alex P · Accepted Answer · 2013-12-31 16:19:13Z

2

This works for your example:

Sub RandNumRang()
Dim Row As Integer, Col As Integer
Dim j As Integer
Dim LastRow As Long
Dim NoRows As Integer
Dim MinNum As Double, MaxNum As Double
Dim NumFormula As String
Col = 1
Row = 2

For j = 3 To 4
    NoRows = Range("G" & j).Value
    MinNum = Range("E" & j).Value
    MaxNum = Range("F" & j).Value
    NumFormula = "=RANDBETWEEN(" & MinNum & "," & MaxNum & ")"

    For Row = Row To (Row + NoRows - 1)
        Cells(Row, Col).Value = Row - 1
        Cells(Row, Col + 1).Formula = NumFormula 'Random Value
        Cells(Row, Col + 2).FormulaR1C1 = "=TIME(0,0,RC[-1])" 'Convert to time
    Next Row
Next j
End Sub

answered Dec 31, 2013 at 16:19

Alex P

12.5k7 gold badges55 silver badges74 bronze badges

1 Comment

eroma934 Over a year ago

Perfect! I appreciate it.

Collectives™ on Stack Overflow

VBA: Second loop overwriting first loop

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related