CAnnot get RANDBETWEEN function and Autofill to work in VBA

[Harryf]

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):

    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:

ActiveCell.FormulaR1C1 = WorksheetFunction.RandBetween(Range("Bi").Value, Range("C1").Value) / 100

Please, can anyone help me see the light?

Thank you very much!

Harry

 

[mikerickson]

Try

ActiveCell.FormulaR1C1 = "=RANDBETWEEN(R1C2, R1C3)/100"

 

[Fluff]

You have a typo in your code, it should be B1 not Bi

 

[mikerickson]

@Fluff, actually I had it in A1 style instead of R1C1. It should be fixed now.

 

[Fluff]

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

 

[Harryf]

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]

What about using

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

 

[Harryf]

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

 

[Michael M]

glad we could help...