vba to calculate a randbetween in a range of cells relative to a selected cell

cjcass

Well-known Member
Joined
Oct 27, 2011
Messages
575
Office Version
  1. 2016
Platform
  1. Windows
Hi,
I'm looking for some vba that will do the following if possible:
Example: cell R1 contains the following: '1>5' and when the macro is run it puts a 'formula' into the range from R3: down to the last row containing data in Col A (eg.R3:R7540). The formula calculates a 'Randbetween 1 and 5' for every cell in the range and then converts them to values so no formulas are left. The example here shows R1 and then R3 down for the range, however I need the vba to look at whatever cell is selected and then offest 2 rows down as the starting point fo the range, so if cell AB1 was selected before I push the macro button then it would take the contents in AB1 (eg.'3>8') and set a randbetween 3 and 8 for every cell from AB3: down to the last row containing data in Col A.
Hope this makes sense?
Any help much apprecatied.
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
See if this does what you want. Note that it does not check
- if the selected cell contains a ">" sign
- if the values each side of the ">" sign are numerical
- if the left number is <= the right number

Of course if any of those things fail, then so will the code.

Code:
Sub R_Between()
  Dim LowHigh As Variant
  
  If Selection.Cells.Count = 1 Then
    LowHigh = Split(Selection.Value, ">")
    With Selection.Offset(2).Resize(Range("A" & Rows.Count).End(xlUp).Row - Selection.Row - 1)
      .Formula = "=randbetween(" & LowHigh(0) & "," & LowHigh(1) & ")"
      .Value = .Value
    End With
  End If
End Sub

.. or perhaps this instead
Code:
Sub R_Between_2()
  If Selection.Cells.Count = 1 Then
    With Selection.Offset(2).Resize(Range("A" & Rows.Count).End(xlUp).Row - Selection.Row - 1)
      .Formula = "=randbetween(" & Replace(Selection.Value, ">", ",") & ")"
      .Value = .Value
    End With
  End If
End Sub
 
Last edited:

cjcass

Well-known Member
Joined
Oct 27, 2011
Messages
575
Office Version
  1. 2016
Platform
  1. Windows
Works perfectly, I went for the 2nd one.
Many thanks for your help with this. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,892
Messages
5,598,702
Members
414,254
Latest member
MarieCo

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
Top