VBA - Formula pasting as #NAME?

[MarkCBB]

Hi there,

I have a Workbook with 2 worksheets, on Sheet1 there is a list of Competition winners, in Column A on Sheet1 there is a unique entry number. i.e. 123249. On Sheet2 there is a table column A consists of a Heading "#" then 1-26 under that until row 27. Column B has the heading Winners.

I am using the following Code to select 26 random numbers from Sheet1 and place then in the Sheet2 table.

Sub Winners()
Do
    ActiveCell.FormulaR1C1 = "=RANDBETWEEN(MIN(Sheet2!A:A),MAX(Sheet2!A:A))"
    ActiveCell.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell.Offset(0, 1))
End Sub

However I get a formula error #NAME? I am not sure why this is happening? does anyone know?

I am also trying to find a way on ensuring that no duplicate numbers are selected? but I am not sure on how to approach this.

I am also new to VBA.
Using Excel 2010.

Thanks.

 

[VoG]

Try

ActiveCell.Formula = "=RANDBETWEEN(MIN(Sheet2!A:A),MAX(Sheet2!A:A))"

 

[MarkCBB]

Hello VoG,

ahhhh, I see. the R1C1 is is only for formulas that contain R1C1 refernces. I fell silly now. thanks.

This is my amended Code, as I have made a few changes to the formula so that it has a date range to reffer to.

Sub Winners2()
Do
    ActiveCell.Formula = "=RANDBETWEEN(MIN(IF(Sheet2!B:B>=Sheet1!$D$1,Sheet2!A:A)),MAX(IF(Sheet2!B:B<=Sheet1!$F$1,Sheet2!A:A)))"
    ActiveCell.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -1))
End Sub

This works perfectly however sometimes some of the winners are the same. somehow I need to add to this code to say that if any of the cells values are the same as the active value it must recalculate the random number.

I am not to sure how to do this. can you assit?

 

[VoG]

Perhaps

Sub Winners2()
Do
    ActiveCell.Formula = "=RANDBETWEEN(MIN(IF(Sheet2!B:B>=Sheet1!$D$1,Sheet2!A:A)),MAX(IF(Sheet2!B:B<=Sheet1!$F$1,Sheet2!A:A)))"
    Do While WorksheetFunction.CountIf(ActiveCell.EntireColumn, ActiveCell.Value) > 1
        Calculate
    Loop
    ActiveCell.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -1))
End Sub

 

[MarkCBB]

Thank-you that works. But I just realized another challenge.

I am randomly generating a number between the Min and MAX range that meets the date range Criteria. But I didn't take into account that the number that was being randomly generated might not be an entry on Sheet1. How to I randomly pick a number from a list of numbers?

The only way that I can think of is to (Randbetween the start row and end row) of the Lookup range and then Index/Match, the row number to the corresponding Unique number.

Do you perhaps have a better way of doing it? or should I stick to the above?

Look forward to your insight. thanks

 

[VoG]

Try:

Generate a random number between 1 and the length of your list of numbers.

Use worksheetfunction.index to retrieve that number from the list.

 

[MarkCBB]

Thanks VoG, I will give that a try.

Thanks for the assistance.