Select range size determined by value in cell

Hi,

I require a VBA code which selects a range of cells and fills it with a formula. The number of cells to select can be found in cell C1. The value in cell C1 will change every month.

For example, this month the value will be 87. Therefore i would like to select 87 cells from B3 downward. I then want to fill in the selected Cells with the following formula =RANDBETWEEN(1,$C$1).

Can someone help me with code which can do this?

Thank you

Maybe:

Please Login or Register  to view this content.

Thanks for the reply. This works.

However, I'm obviously lacking the some brain capacity today, but when i add this code to existing code i get the 'run time 1004' error.

Code:

ActiveWorkbook.Sheets("Spot Buy Analysis").Activate
Range("B3").Resize(Range("C1").Value).Formula = "=RANDBETWEEN(1, $C$1)"


What am i missing?

Cells B3 and C1 are in the "Spot Buy Analysis" worksheet.

With the above With, End With - i still get the 1004 error

The value of 87 is in C1. Im pretty confused.

Error:
Run-time error '1004'
Application-defined or object-defined error

My Code:

Private Sub CommandButton2_Click()

ActiveWorkbook.Sheets("Master").Activate
ActiveSheet.Cells(2, 1).Select
ActiveSheet.Range("A2:A150000").Select
Selection.FormulaR1C1 = "=Sheet1!RC"
ActiveSheet.Cells(2, 2).Select
ActiveSheet.Range("B2:B150000").Select
Selection.FormulaR1C1 = "=VALUE(LEFT(Sheet1!RC[2],6))"
ActiveWindow.SmallScroll Down:=-12
ActiveSheet.Cells(2, 3).Select
ActiveSheet.Range("C2:C150000").Select
Selection.FormulaR1C1 = "=VALUE(Sheet1!RC[2])"
ActiveSheet.Cells(2, 4).Select
ActiveSheet.Range("D2:D150000").Select
Selection.FormulaR1C1 = "=VALUE(Sheet1!RC[2])"
ActiveWindow.SmallScroll Down:=-6
ActiveSheet.Cells(2, 5).Select
ActiveSheet.Range("E2:E150000").Select
Selection.FormulaR1C1 = "=Sheet1!RC[11]"
ActiveWindow.SmallScroll Down:=-6
ActiveSheet.Cells(2, 6).Select
ActiveSheet.Range("F2:F150000").Select
Selection.FormulaR1C1 = _
"=IF(Master!RC[-4]=IFERROR(VLOOKUP(Master!RC[-4],'OEM-Agent to OEM'!R3C1:R178C10,1,FALSE),""""),""OEM"",IF(Master!RC[-4]=IFERROR(VLOOKUP(Master!RC[-4],'rotables suppliers'!R3C1:R70C10,1,FALSE),""""),""Rotables"",IF(Master!RC[-2]=IFERROR(VLOOKUP(Master!RC[-2],'Contracted Suppliers'!R4C1:R137C6,1,FALSE),""""),""Contracted"",""No"")))"
ThisWorkbook.RefreshAll




ActiveWorkbook.Sheets("Instructions").Activate

ActiveSheet.Cells(20, 4).Select
Selection.FormulaR1C1 = _
"=IFERROR(COUNT(Sheet1!R[-18]C[12]:R[149980]C[12]),"""")"
ActiveSheet.Cells(20, 5).Select
Selection.FormulaR1C1 = _
"=IFERROR(SUM(Sheet1!R[-18]C[11]:R[149980]C[11]),"""")"

ActiveWorkbook.Sheets("Spot Buy Analysis").Activate
Range("B3").Resize(Range("C1").Value).Formula = "=RANDBETWEEN(1, $C$1)"

End Sub

Unfortunately the workbook is too large to upload. I will try and delete some data and upload a smaller version if i can.