Solved

# Problem with Circular References

Posted on 2012-08-22
300 Views
I have a spreadsheet containing percentage numbers. I want to remove those % sign by writing a macro that formats those percentage numbers to general number then mutiply by 100. For example, 4.5% will change to 4.5 after running the macro.  The sample code is below
With Sheets("Sheet1")
.Range("P19").NumberFormat = "General"
.Range("P19").FormulaR1C1 = "=RC*100"
.Range("P19").AutoFill Destination:=.Range("P19:P200"), Type:=xlFillDefault
End With

When running this, all i get is 0 values range from P19 to P200 and I notice there's a Circular References warning in the status bar. How can i fix this?
0
Question by:TracieD

LVL 5

Accepted Solution

Replace     .Range("P19").FormulaR1C1 = "=RC*100"
by     .Range("P19").value =.Range("P19").value * 100

Actually, your loop could reather be:

With Sheets("Sheet1")
For i =  0 to 199
.cells(i+19,16).NumberFormat = "General"
.cells(i+19,16) = .cells(i+19,16) * 100
Next i
End With
0

LVL 92

Expert Comment

No need for a loop, even:

``````Sub Multiply100()

Dim NewWb As Workbook
Dim UseWs As Worksheet

Set UseWs = ActiveSheet
With [a1]
.Value = 100
.Copy
End With

UseWs.Range("p19:p200").PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False

NewWb.Close False

MsgBox "Done"

End Sub
``````
0

## Featured Post

### Suggested Solutions

Generating Excel in php 5 30
Search Box 13 21
Adding Data To Master File From Import Final 9 17
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…