Macro from cell specific to whole column

  • Thread starter Thread starter Bean Counter
  • Start date Start date
B

Bean Counter

Hello All,

I have recorded the following macro...

ActiveCell.FormulaR1C1 = "=RIGHT(RC[-3],1)"
Range("O286").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=""-"",(LEFT(RC[-4],15)*-1),RC[-4])"

that basically takes a text stored number such as 100.54- to -100.54. What
I want to do is make this auto fill for the entire column. How should
rewrite it?
 
Hello All,

I have recorded the following macro...

    ActiveCell.FormulaR1C1 = "=RIGHT(RC[-3],1)"
    Range("O286").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]=""-"",(LEFT(RC[-4],15)*-1),RC[-4])"

that basically takes a text stored number such as 100.54- to -100.54.  What
I want to do is make this auto fill for the entire column.  How should
rewrite it?
Click to expand...

next line
Range(ActiveCell, ActiveCell.End(xlDown)).FillDown
 
Try this version...........

Sub Negsignleft()
Dim Cell As Range
Dim rng As Range
On Error Resume Next
Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
On Error GoTo 0
For Each Cell In rng
If IsNumeric(Cell.Value) Then
Cell.Value = CDbl(Cell.Value) * 1
End If
Next Cell
End Sub


Gord Dibben MS Excel MVP
 
Try the below instead' which will convert any text with trailing - to
numbers...

Sub Macro()
Selection.TextToColumns Destination:=Selection, _
DataType:=xlDelimited, TrailingMinusNumbers:=True
End Sub
 
Don't both with the rewrite.

Once you deposited a good formula in a single cell, use copy/paste to fill
the rest of the column.

After all, if the approach is good enough for a human, it should be good
enough for your macro.
 
Back
Top