• We are receiving reports of members using the private messaging service (Conversations) in ways that break the forum rules:
    • Do not invite another member to take the question off the forum (i.e. do not suggest that they post to a different forum, do not suggest that they email or private message you the problem, do not simply post a link to another forum, unless it is to a specific, relevant, thread). This applies equally to members asking or answering questions.
    • Soliciting business for yourself is not permitted. This is an all volunteer board, so offering solutions in exchange for compensation is not permitted. Likewise, members seeking solutions must not offer compensation for them. If you have an urgent need, check the Consulting Services page.
    Please help us out by clicking the Report link on any messages you receive that violate these rules. Thank you
  • If you would like to post, please check out the MrExcel Message Board FAQ and click here to register.
    If you forgot your password, you can reset your password.

fill a range with numbers - vba

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,488
Hi
The code below is going to fill a range with random number but all the cells will get the same number. What I want, is to fill cells of that range with different random number. So I am thinking to put that line of code in a loop but how can I loop when user might enter any different range? How you would do that please. Thank you very much.
Code:
Sub cells_fill()
    Dim x as integer
    Dim y As Integer
    Dim n As Integer
    Dim m As Integer
    x = InputBox("row1")
    y = InputBox("col1")
    n = InputBox("row2")
    m = InputBox("col2")
    Range(Cells(x, y), Cells(n, m)).Value = WorksheetFunction.RandBetween(1, 100)
End Sub
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
17,960
Office Version
2013
Platform
Windows
Try

Code:
Sub MM1()
Dim x As Integer, y As Integer
Dim n As Integer, m As Integer, cell As Range
x = InputBox("row1")
y = InputBox("col1")
n = InputBox("row2")
m = InputBox("col2")
For Each cell In Range(Cells(x, y), Cells(n, m))
    cell.Value = WorksheetFunction.RandBetween(1, 100)
Next cell
End Sub
 

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,488
Thank you very much. Never thought of for-each. So happy to know it. thank you again.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,689
Or perhaps one dialog box and no loop.

Code:
Dim RangeToFill As Range

On Error Resume Next
Set RangeToFill = Application.InputBox("Select a range to fill with the mouse", Type:=8)
On Error Goto 0

If RangeToFill Is Nothing then Exit Sub: Rem cancel pressed

With RangeToFill
    .Formula = "=RANDBETWEEN(1, 100)"
    .Value = .Value
End With
 
Last edited:

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,488
Wow, thanks Mike. So interesting to learn different style. Thanks a lot again
 

Forum statistics

Threads
1,077,962
Messages
5,337,434
Members
399,147
Latest member
Raviteja KOTHA

Some videos you may like

This Week's Hot Topics

Top