fill a range with numbers - vba

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,805
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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
 
Upvote 0
Thank you very much. Never thought of for-each. So happy to know it. thank you again.
 
Upvote 0
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:
Upvote 0
Wow, thanks Mike. So interesting to learn different style. Thanks a lot again
 
Upvote 0

Forum statistics

Threads
1,212,931
Messages
6,110,745
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top