Circular reference issue

[Afrooman108]

A random number between 1 and 30 is generated this applies to a Personal no.

I have a button that produces a number 1 in a cell.

There are then a series of 30 if statements that will produce a true or false if the corresponding personal no. is the same as the random number generated and the button has been pressed.

I want to have a running count of how many true outcomes there are, but in most of my attempts i get a circular Reference or something similar.

Cheers

 

[PaddyD]

You need to tell us more about where the data is on the sheet. A worked example of what you're after would help.

 

[Afrooman108]

well the random number between 1 and 30 is in c3, the button created '1' is in A29. these are in a workbook called 'WHO'.
The if statements are in another workbook called 'THEM' they are from G2-G31 this is the if statement =IF(AND(Who!$C$3 = Them!A2, Who!$A$29 = 1), 1,) The A2 increases with each row.
I want to record the running count in D2-D31 so ive got this kind of idea =D2+G2. when D2 = 0 And G2=1 then D2 has 1 added and become 1 but then when g2 becomes 0 again D2 also becomes 0.

 

[StephenCrump]

I want to record the running count in D2-D31 so ive got this kind of idea =D2+G2.

There's your circular reference, if you set D2=D2+G2!

In simple terms, it sounds like what you want to do is run RANDBETWEEN(1,30) N times, and count the number of times 1 occurs, 2 occurs .... 30 occurs?

 

[Afrooman108]

Yes, but only after the button is pressed.

 

[StephenCrump]

Does this do what you want? (Assuming numbers 1 to 30 in Cells A1:A30, and runnning totals in B1:B30):

Sub RunningCount()   'Your button to trigger this
    
    Dim rng As Range
    Dim lRandom As Long
    Dim lRepeats As Long
    Dim i As Long
    
    Set rng = [B1:B30]
    lRepeats = 1   'Vary as you like
    
    For i = 1 To lRepeats
        lRandom = WorksheetFunction.RandBetween(1, 30)
        rng(lRandom) = rng(lRandom) + 1
    Next i
    
End Sub

Sub ResetRunningCount()

    [B1:B30].ClearContents

End Sub

 

[Afrooman108]

Yeah, thats great m8, ill change it a little but thanks very much never the less forevermore.
Could i change it to 1random = [A29], just to check.

 

[Afrooman108]

okay this is embarrassing how do i reference a cell in another workbook? is it like this? Set rng = Students.[D2:D31]

 

[StephenCrump]

No need for embarrassment! Example code for:

Another sheet:

MyVariable = Sheets("Sheet2").Range("A1")

Another workbook:

Range("A1:A4") = Workbooks("Book2.xlsx").Sheets("Sheet1").Range("A1:A4")

No need to Set a range object for simple one-offs like this, but if you're going to do several things with the range:

Set rng = Workbooks("Book2.xlsx").Sheets("Sheet1").Range("A1:A4")
With rng
    .Name = "YourNewRangeName"
    .Font.Bold = True
    .Interior.Color = vbYellow
    .Cells(1, 1) = "This is the first cell in the range"
End With

 

[Afrooman108]

so now i want to move cells... What i need to happen is for a particular cell within a column of cells to be removed and for all the cells below it to be shifted up, not the rows the cell data. here is what ive got i think it will work but i havent got any idea of the correct syntax.
where A1 to A30 contains a list of names
G14 contains the 'A' column cell to be deleted
Set range A1:A30
if indirect('B'&G14) = 'X' then --------- Or If (Range2forB1:B30)(G14) = 'X' then
[Range(G14):A30].offset(1,0) = [Range(G14):A30]
[A30].clearcontentsendif