Circular reference issue

Afrooman108

New Member
Joined
Mar 1, 2012
Messages
13
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

New Member
Joined
Mar 1, 2012
Messages
13
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

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,250
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 1, 2012
Messages
13

ADVERTISEMENT

Yes, but only after the button is pressed.
 

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,250
Office Version
  1. 365
Platform
  1. Windows
Does this do what you want? (Assuming numbers 1 to 30 in Cells A1:A30, and runnning totals in B1:B30):

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

New Member
Joined
Mar 1, 2012
Messages
13

ADVERTISEMENT

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

New Member
Joined
Mar 1, 2012
Messages
13
okay this is embarrassing how do i reference a cell in another workbook? is it like this? Set rng = Students.[D2:D31]
 

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,250
Office Version
  1. 365
Platform
  1. Windows
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:

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

New Member
Joined
Mar 1, 2012
Messages
13
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
 

Forum statistics

Threads
1,141,498
Messages
5,706,695
Members
421,465
Latest member
Dunners

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top