StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POVBA: Second loop overwriting first loop
text
Body
copied!<p>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:</p> <pre><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 </code></pre> <p>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 <em>j</em> 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</p> <pre><code>For Row=2 To NoRows + 1 </code></pre> <p>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.</p> <p>Also, I have attached a screenshot of the worksheet for reference:</p> <p><img src="https://i.stack.imgur.com/NUM3J.png" alt="enter image description here"></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload