CAnnot get RANDBETWEEN function and Autofill to work in VBA

Harryf

New Member
Joined
Aug 31, 2013
Messages
34
Hi there!

I am using VBA to do the following:

I use it to write the RANDBETWEEN function in, say, cell A1.
I then use the AUTOFILL command to copy the function down to, say, A10.
I then highlight the range A1 through A10 and Copy/Paste the values generated by the Randbetween function copied down to Cell A10 in place.

All works perfectly as long as I enter specific minimum and maximum values in the RANDBETWEEN function.

The code for the first two steps looks like this (ActiveCell = Cell A1):
Code:
    ActiveCell.FormulaR1C1 = "=RANDBETWEEN(-5,15)/100"
    Selection.Style = "Percent"
    Selection.NumberFormat = "0.00%"
    Selection.AutoFill Destination:=Range("A1:A10"), Type:=xlFillDefault

BUT HERE IS MY PROBLEM:

The figures -5 and 15 in the function varies based on values of other cells and I want to replace the figures in the code above with some sort of code that would reference those varying values. The minimum value (the -5 in the code above) is based on the value in Cell B1, for example, and the maximum value (the 15 in the code above) is based on the value of Cell C1.

I have been trying to use all sorts of things like replacing the -5 in the code above with Range("C1").Value, but I keep getting an "Expected End of Statement" error in VBA.

I then used the following code, but it places a VALUE in Cell A1, which simply repeats that single result of the Randbetween function value when copied down...

This is that code that does not work:
Code:
ActiveCell.FormulaR1C1 = WorksheetFunction.RandBetween(Range("Bi").Value, Range("C1").Value) / 100

Please, can anyone help me see the light?

Thank you very much!

Harry
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,780
Try
Code:
ActiveCell.FormulaR1C1 = "=RANDBETWEEN(R1C2, R1C3)/100"
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,349
Office Version
  1. 365
Platform
  1. Windows
You have a typo in your code, it should be B1 not Bi
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,349
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

@mikerickson
Hadn't actually seen you post & was referring to the OP's code.
 

Harryf

New Member
Joined
Aug 31, 2013
Messages
34
Hi guys!

Thanks for the answer, Mike! I did get the same results as far as the R1C1 style is concerned when recording my steps using the "record macro" function, but because I am not au fait with that style, I chose to ignore it, not realising that it also works even when my system is set up in A1 style.

Fluff, you are right about the typo in my post... Those cells were, however, just examples in my post and not the actual cells referenced in my file, where, luckily, there were no typos...

Just too bad that the "Range...Value" VBA thing does not seem to work, with or without the typos... hehe

Thank you very much for the answer Mick and the input Fluff!!

Hope yours is a great week!
h
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,261
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

What about using

Code:
Sub MM1()
With Range("A1:A10")
    .Formula = "=RandBetween(" & Range("B1").Value & "," & Range("C1").Value & ") / 100"
    .NumberFormat = "0.00%"
End With
End Sub
 
Last edited:

Harryf

New Member
Joined
Aug 31, 2013
Messages
34
That is BRILLIANT, Michael!

That's the kind of thing I thought I was looking for, actually. I'll sit down some day and figure out how the code does what it does...

For now I am just chuffed that both your code and that of Mike work perfectly.

Very thankful for your help!

h
 

Watch MrExcel Video

Forum statistics

Threads
1,109,050
Messages
5,526,494
Members
409,704
Latest member
saialkesh

This Week's Hot Topics

Top