# Circular reference issue

#### Afrooman108

##### New Member
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 change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

##### MrExcel MVP
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
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
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
Yes, but only after the button is pressed.

#### StephenCrump

##### MrExcel MVP
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
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
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
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
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

Replies
6
Views
323
Replies
0
Views
534
Replies
3
Views
610
Replies
4
Views
718
Replies
7
Views
906

1,171,463
Messages
5,875,657
Members
433,145
Latest member
nzltrippa

### 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.

### Which adblocker are you using?

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

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