• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 355
  • Last Modified:

Problem with Circular References

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?
1 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
Patrick MatthewsCommented:
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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now