Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retreive part of the value from each cell - Macro and/or forumula
Hi Group,
I have the following values in the cells A5 thru A11106. I am trying to have a macro or a forumula to have the values after = and without commas in its corresponding cell in the column B. [1] =,26, [2] =,126, [3] =,5626, [4] =,276, [5] =,228896, ..... ..... ..... Result: 26 126 5626 276 228896 The following values in the cells C5 thru C11106. I am trying to write macro to have the values after = and without commas in its corresponding cell in the column D. b[1] =,26, nuiio[2] =,126gf, f[3] =,g5626, dw[4] =,276, eef[5] =,228896, ..... ...... Result: 26 126gf g5626 276 228896 I've already in the process of debugging my formula/code to achieve this but no luck so far. Thought of writing to the group... Please let me know how can I achieve this. Thanks, Kevin |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retreive part of the value from each cell - Macro and/or forumula
A1: ,26
B1: =SUBSTITUTE(A1,",","") Or: Public Sub m() On Error GoTo ErrorHandler Dim sh As Worksheet Dim lLastRow As Long With Application .ScreenUpdating = False .Calculation = xlManual .StatusBar = "Sto eseguendo: Sub m()" End With Set sh = Worksheets("Sheet1") With sh lLastRow = _ .Range("A" & Rows.Count).End(xlUp).Row .Range("B1").Value = _ "=SUBSTITUTE(A1,"","","""")" .Range("B1").Select Selection.AutoFill _ Destination:=.Range("B1:B" & lLastRow) End With ExitRow: Set sh = Nothing With Application .ScreenUpdating = True .Calculation = xlAutomatic .StatusBar = "" End With Exit Sub ErrorHandler: MsgBox Err.Number & vbNewLine & Err.Description Resume ExitRow End Sub -- --------------------------- Mauro Gamberini http://www.riolab.org/ ha scritto nel messaggio ... Hi Group, I have the following values in the cells A5 thru A11106. I am trying to have a macro or a forumula to have the values after = and without commas in its corresponding cell in the column B. [1] =,26, [2] =,126, [3] =,5626, [4] =,276, [5] =,228896, .... .... .... Result: 26 126 5626 276 228896 The following values in the cells C5 thru C11106. I am trying to write macro to have the values after = and without commas in its corresponding cell in the column D. b[1] =,26, nuiio[2] =,126gf, f[3] =,g5626, dw[4] =,276, eef[5] =,228896, .... ..... Result: 26 126gf g5626 276 228896 I've already in the process of debugging my formula/code to achieve this but no luck so far. Thought of writing to the group... Please let me know how can I achieve this. Thanks, Kevin |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retreive part of the value from each cell - Macro and/or forumula
On Oct 28, 10:46*am, "Mauro Gamberini"
wrote: A1: ,26 B1: =SUBSTITUTE(A1,",","") Or: Public Sub m() On Error GoTo ErrorHandler * * Dim sh As Worksheet * * Dim lLastRow As Long * * With Application * * * * .ScreenUpdating = False * * * * .Calculation = xlManual * * * * .StatusBar = "Sto eseguendo: Sub m()" * * End With * * Set sh = Worksheets("Sheet1") * * With sh * * * * lLastRow = _ * * * * * * .Range("A" & Rows.Count).End(xlUp).Row * * * * .Range("B1").Value = _ * * * * * * "=SUBSTITUTE(A1,"","","""")" * * * * .Range("B1").Select * * * * Selection.AutoFill _ * * * * * * Destination:=.Range("B1:B" & lLastRow) * * End With ExitRow: * * Set sh = Nothing * * With Application * * * * .ScreenUpdating = True * * * * .Calculation = xlAutomatic * * * * .StatusBar = "" * * End With * * Exit Sub ErrorHandler: * * MsgBox Err.Number & vbNewLine & Err.Description * * Resume ExitRow End Sub -- --------------------------- Mauro Gamberinihttp://www.riolab.org/ ha scritto nel ... Hi Group, I have the following values in the cells A5 thru A11106. I am trying to have a macro or a forumula to have the values after = and without commas in its corresponding cell in the column B. [1] =,26, [2] =,126, [3] =,5626, [4] =,276, [5] =,228896, .... .... .... Result: 26 126 5626 276 228896 The following values in the cells C5 thru C11106. I am trying to write macro to have the values after = and without commas in its corresponding cell in the column D. b[1] =,26, nuiio[2] =,126gf, f[3] =,g5626, dw[4] =,276, eef[5] =,228896, .... ..... Result: 26 126gf g5626 276 228896 I've already in the process of debugging my formula/code to achieve this but no luck so far. Thought of writing to the group... Please let me know how can I achieve this. Thanks, Kevin- Hide quoted text - - Show quoted text - Thanks guys... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Retreive part of the value from each cell - Macor and/or forumula | Excel Discussion (Misc queries) | |||
How to retreive the name of a named cell in an excel macro | Excel Programming | |||
Macro affecting hyperion retreive | Excel Programming | |||
Help with forumula to change text cell | Excel Programming | |||
Is there a forumula that uses colors instead of cell #'s? | Excel Discussion (Misc queries) |