[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 351
  • 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


Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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