Macro from cell specific to whole column
 

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?
--
Thanks for all of the help. It is much appreciated!!!!

Macro from cell specific to whole column
 

On May 26, 5:06*am, Bean Counter
wrote:
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?
--
Thanks for all of the help. *It is much appreciated!!!!

next line
Range(ActiveCell, ActiveCell.End(xlDown)).FillDown

Macro from cell specific to whole column
 

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

On Tue, 25 May 2010 14:06:01 -0700, Bean Counter
wrote:

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?

Macro from cell specific to whole column
 

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

--
Jacob (MVP - Excel)


"Bean Counter" wrote:

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?
--
Thanks for all of the help. It is much appreciated!!!!

Macro from cell specific to whole column
 

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.
--
Gary''s Student - gsnu201003


"Bean Counter" wrote:

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?
--
Thanks for all of the help. It is much appreciated!!!!