VBA WorksheetFunction dynamic randbetween

Question

hope all is well.

I am slightly stuck on a VBA function called randbetween in Excel.

Nature of the problem is that I need to create random numbers based on a bunch of other numbers, about 50,000 other numbers in total.

The random numbers I generate must be between 1 and X. X being the other numbers in column D1:D50,000.

As an example: if cell D1 contains the number 5, then I need to create a random number between 1 and 5 in Cell A1. then move on to D2,D3,D4.....etc and create random numbers for each one accordingly, A2,A3,A4...etc.

I tried to use the following but unfortunately the offset part doesn't work. I want to dynamically work through each cell.

the code is as follows:

r = WorksheetFunction.RandBetween(1, Offset(A1, n, 9))
'where n = 2

Most grateful for any help,

please show the wider scope of your code, not only one single line... — Kazimierz Jawor
– Kazimierz Jawor, Commented Dec 7, 2013 at 21:09
Thanks KazJaw, not sure exactly what I can add with regards to the wider scope to the code. For some reason Excel VBA doesn't seem to accept the offset function as part of a nested randbetween function. The range code Chris highlighted below seems to do exactly what is needed. This is part of a bigger code I am writing in Access database but I was stuck at the first part. Any thoughts on why original code didn't work would be useful. Is it just a quirk of Excel? Not sure. Thanks for coming back so quickly. — user2259146
– user2259146, Commented Dec 7, 2013 at 21:43

chris neilsen · Accepted Answer · 2013-12-07 21:23:10Z

1

Your use of OFFSET is the wrong syntax. You would need somthing like

Range("A1").Offset(RowOffset, ColumnOffset)

But there is a much better approach to achieve your stated goal. Use Range.FormulaR1C1

Sub Demo()
    Dim rng As Range

    ' Define range
    Set rng = [A1:A50000]

    ' Put formulas into the range
    rng.FormulaR1C1 = "=RANDBETWEEN(1,RC4)"

    'optional, replace formulas with values
    rng.Value = rng.Value

End Sub

answered Dec 7, 2013 at 21:23

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

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

2 Comments

user2259146 Over a year ago

Thanks for getting back so quickly. I will have a look and get back asap.

user2259146 Over a year ago

The Range works perfectly. So my vba becomes: p = WorksheetFunction.RandBetween(1, Range("A1").Offset(2, 9))

Collectives™ on Stack Overflow

VBA WorksheetFunction dynamic randbetween

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related