• 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?
0
TracieD
Asked:
TracieD
1 Solution
 
sbaughanCommented:
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
 
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
        .Copy
    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

0
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