Problem with Circular References

Posted on 2012-08-22
Last Modified: 2012-08-22
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?
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
    LVL 92

    Expert Comment

    by:Patrick Matthews
    No need for a loop, even:

    Sub Multiply100()
        Dim NewWb As Workbook
        Dim UseWs As Worksheet
        Application.DisplayAlerts = False
        Set UseWs = ActiveSheet
        Set NewWb = Workbooks.Add
        With [a1]
            .Value = 100
        End With
        UseWs.Range("p19:p200").PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply, _
            SkipBlanks:=False, Transpose:=False
        NewWb.Close False
        Application.DisplayAlerts = True
        MsgBox "Done"
    End Sub

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Suggested Solutions

    Title # Comments Views Activity
    Generating Excel in php 5 30
    how to add and add vat in excel 9 26
    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…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now