Prev: Use Macro To Change Which Macro Assigned To Command Button
Next: Programmable Column Sequencing?
From: Bean Counter on 25 May 2010 17:06
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!!!!
From: PY & Associates on 26 May 2010 04:52
On May 26, 5:06 am, Bean Counter
<BeanCoun...(a)discussions.microsoft.com> 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
From: Gord Dibben on 26 May 2010 11:59
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
<BeanCounter(a)discussions.microsoft.com> 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?

From: Jacob Skaria on 27 May 2010 17:45
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!!!!
From: Gary''s Student on 27 May 2010 18:04
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!!!!
 | 
Pages: 1
Prev: Use Macro To Change Which Macro Assigned To Command Button
Next: Programmable Column Sequencing?