Macro from cell specific to whole column

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?
 
P

PY & Associates

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?

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

Gord Dibben

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
 
J

Jacob Skaria

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
 
G

Gary''s Student

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top