If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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!!!! |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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!!!! |
#5
|
|||
|
|||
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!!!! |
Thread Tools | |
Display Modes | |
|
|