Offset an entire row with .End(xlUp) + offset + .value (in VBA)

Question

I have been searching for similar questions and haven't found this specific one, so apologies if it has already been asked.

I am trying to backtest an idea in finance and run 100's of simulations. My intention is to add a row (or just a range of cells in a row) of preset values into the next empty row in a data set. Based on the random number that is generated in cell "A1", 1-9, it should select a specific row and copy/paste or offset the data into the next empty row. Then the timer ticks down, and restarts.

What is below works, but requires me to hand enter each cell origin to cell destination, and I would need 40+ cells for each of the (1-9 possibilities of the randomization). So that would be a lot of hard coding. There has to be a better way.

Thanks in advance.

Sub CD1()
 
start_value = WorksheetFunction.RandBetween(1, 9)
Range("A1") = start_value
If Range("A1") < 5 Then
    Range("c5000").End(xlUp).Offset(1, 0) = Range("c6").Value
    Range("d5000").End(xlUp).Offset(1, 0) = Range("d6").Value
    Range("e5000").End(xlUp).Offset(1, 0) = Range("e6").Value
    Range("f5000").End(xlUp).Offset(1, 0) = Range("f6").Value
End If

Application.OnTime Now + TimeValue("00:00:03"), "Sheet5.CD1"

End Sub

Have a look e.g. this question to copy/paste range. If you stuck with it please edit your question of the issue. — Black cat
– Black cat, Commented Mar 7 at 9:34
it should select a specific row how is this determined ? Your code shows row 6 selected for random numbers 1 to 4 — CDP1802
– CDP1802, Commented Mar 7 at 9:37
Inside the If statement, for e.g., 50 columns, try Dim CurrentRow As Long: CurrentRow = Cells(Rows.Count, "C").End(xlUp).Offset(1).Row and continue with Rows(CurrentRow).Columns("C:AZ").Value = Range("C6:AZ6").Value. If you mean something else, explain in more detail. — VBasic2008
– VBasic2008, Commented Mar 7 at 9:50
It might help if you walked through an example showing the starting point, pick a small number and then show the results you expect. Repeat once so the pattern is clear. stackoverflow.com/posts/79491792/edit your question, don't put this detail in a comment — CHill60
– CHill60, Commented Mar 7 at 10:22

Dave Lett · Accepted Answer · 2025-03-07 11:14:49Z

0

I agree with the initial comments, but you said your routine is working and that you wanted it improved so that you didn't have to use specific cell references. The code below assumes that you always want to append the contents from Row 6.

Sub CD1()
Dim start_value As Integer
Dim R As Range
'''based on your post "What is below works",
'''this is the range you always want to paste next
Set R = Range("C6:F6")

start_value = WorksheetFunction.RandBetween(1, 9)
Range("A1") = start_value
If Range("A1") < 5 Then
    R.Offset(Cells(Rows.Count, 3).End(xlUp).Row - 5, 0).Value = R.Value
End If

Application.OnTime Now + TimeValue("00:00:03"), "Sheet5.CD1"
End Sub

answered Mar 7 at 11:14

Dave Lett

842 bronze badges

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

Collectives™ on Stack Overflow

Offset an entire row with .End(xlUp) + offset + .value (in VBA)

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related