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

x
  • 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?
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

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