I want to generate random numbers in my model. Every time I run the macro my cells range "G8:H34" have to fill in with different random numbers that should be integers from 0 to 2.

I have to generate those numbers in VBA, because I need to use them in order to optimize a model for a minimal costs and use loop after that to find the best combination that minimizes the cost.

  • 4
    Welcome to Super User. (0) What software are you running? Microsoft Office Excel? OpenOffice? LibreOffice? Lotus 123? Please edit your question and tag it with the relevant product. (1) Please phrase your question in the form of a question. Questions that have a question mark in them somewhere are better received than those that just read like a wish list. (2) We are not a script-writing service, and especially not a do-your-homework-for-you service. Please make an effort to solve your problem on your own. If you get stuck, come tell us what you’ve tried and where you’re having difficulties. Nov 8, 2014 at 1:10
  • So you are allowed to use =rand() or =randbetween(x,y) instead of VBA?
    – Tyson
    Nov 8, 2014 at 2:20

2 Answers 2


You don't need VBA for this. Just use RANDARRAY([rows], [columns], [min], [max], [whole_number])

In G8 put this

=RANDARRAY(27, 2, 0, 2, TRUE)

and random numbers will be filled in the range G8:H34 automatically



Sub qwerty()
    Dim r As Range
    Set r = Range("G8:H34")
    With r
        .Formula = "=randbetween(0,2)"
        .PasteSpecial (xlPasteValues)
    End With
End Sub

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.