assigning a formula to all cells in a range

Aclare87

New Member
Joined
Mar 23, 2012
Messages
24
Hey,

I'm trying to apply the = Round formula to all cells within a range (I'm ultimately trying to set all (-) values in a range to red with surrounding parenthesis and set to 2 decimal points. If I don't round, when I insert the (), it shows out to about 8 decimal points even though i'd already set the numberformat = "0.00".

The round formula isn't working when I use an if statement. I'd like to find out why that's not working, but further, would like to know if it's possible to apply a formula to an entire range.

What I have so far below:

'Name raw data as range
ActiveWorkbook.ActiveSheet.Names.Add Name:="RawSectorData", RefersToR1C1:="=R6C1:R29C11"
ActiveSheet.Name = "RawData"

'Add new sheet and paste data
ActiveWorkbook.Sheets.Add.Name = "SectorSumTop"
ActiveWorkbook.Sheets("RawData").Range("RawSectorData").Copy Destination:=Range("A1")
cells.EntireColumn.AutoFit
'Isolate data
Range("A2,A7:A24").EntireRow.delete
Columns("F").Copy Destination:=Columns("B")
Columns("C").delete
Columns("E:J").delete

'format data - blue, 10, arial narrow, (-) red with ()
ActiveWorkbook.ActiveSheet.Names.Add Name:="SectorTop", RefersToR1C1:="=R2C2:R5C4"
With Range("SectorTop")
.numberformat = "0.00"
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Font.Color = RGB(0, 51, 102)
.Font.Name = "Arial Narrow"
.Font.Size = 10
End With

For Each cell In Range("SectorTop")
If cell.Value < 0 Then
cell.Font.Color = RGB(255, 51, 0)
cell = "=round(" & cell & ",2)"
cell.Value = "'(" & cell & ")"
End If
Next cell

And generally looking for some variation of:

Range("SectorTop").Formula = "=Round(& cell & ,2)"


Thanks!
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
I was able to find a workaround but i'm sure there must be an easier way. Anyone?

'Name raw data as range
ActiveWorkbook.ActiveSheet.Names.Add Name:="RawSectorData", RefersToR1C1:="=R6C1:R29C11"
ActiveSheet.Name = "RawData"
'Add new sheet and paste data
ActiveWorkbook.Sheets.Add.Name = "SectorSumTop"
ActiveWorkbook.Sheets("RawData").Range("RawSectorData").Copy Destination:=Range("A1")
cells.EntireColumn.AutoFit
'Isolate data
Range("A2,A7:A24").EntireRow.delete
Columns("F").Copy Destination:=Columns("B")
Columns("C").delete
Columns("E:J").delete
'format data - blue, 10, arial narrow, (-) red with ()
ActiveWorkbook.ActiveSheet.Names.Add Name:="SectorTop", RefersToR1C1:="=R2C2:R5C4"
With Range("SectorTop")
.numberformat = "0.00"
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Font.Color = RGB(0, 51, 102)
.Font.Name = "Arial Narrow"
.Font.Size = 10
End With
For Each cell In Range("SectorTop")
If cell.Value < 0 Then
cell.Select
activecell.Formula = "=ROUND(" & activecell & " ,2)"
activecell.Copy
activecell.PasteSpecial Paste:=xlPasteValues
cell.Font.Color = RGB(255, 51, 0)
cell.Value = "'(" & cell & ")"
End If
Next cell
Range("SectorTop").Copy



Thanks.
 
Upvote 0

Forum statistics

Threads
1,211,680
Messages
6,103,249
Members
447,850
Latest member
thebuzzman15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top