Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
want Replace ALT+ENTER to SPACE
Hi All,
I wrote one macro for replace for ALT+ENTER to SPACE. It's work well. But if the ONE particular cell (like A1) contains more than 200 entires(1234, 4567, 7895,.. etc) means , at that time it will not work, it says "Formula is too long" . Please give a suggesstion in this case. Thanks in advance for your reply. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
want Replace ALT+ENTER to SPACE
maybe you should post your makro.
arno |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
want Replace ALT+ENTER to SPACE
Sub CleanCR()
Selection.Replace What:=Chr(10), _ Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False End Sub "arno" wrote: maybe you should post your makro. arno |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
want Replace ALT+ENTER to SPACE
Modified from a previous post:
Option Explicit Sub testme01() Dim FoundCell As Range Dim ConstCells As Range Dim BeforeStr As String Dim AfterStr As String BeforeStr = vbLf AfterStr = " " With ActiveSheet Set ConstCells = Nothing On Error Resume Next Set ConstCells = .Cells.SpecialCells(xlCellTypeConstants, _ xlTextValues) On Error GoTo 0 If ConstCells Is Nothing Then MsgBox "Select some cells in the used range" Exit Sub End If With ConstCells 'get as many as we can in one step .Replace what:=BeforeStr, Replacement:=AfterStr, _ lookat:=xlPart, SearchOrder:=xlByRows Do Set FoundCell = .Cells.Find(what:=BeforeStr, _ after:=.Cells(1), _ LookIn:=xlValues, _ lookat:=xlPart, _ SearchOrder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False) If FoundCell Is Nothing Then 'done, get out! Exit Do End If FoundCell.Value _ = Replace(FoundCell.Value, BeforeStr, AfterStr) Loop End With End With End Sub If you're using xl97, change that Replace() to application.substitute() If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ps. Try it against a copy of your data--just in case! Avadivelan TCS wrote: Hi All, I wrote one macro for replace for ALT+ENTER to SPACE. It's work well. But if the ONE particular cell (like A1) contains more than 200 entires(1234, 4567, 7895,.. etc) means , at that time it will not work, it says "Formula is too long" . Please give a suggesstion in this case. Thanks in advance for your reply. -- Dave Peterson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
want Replace ALT+ENTER to SPACE
Hi Dave Peterson,
Good Morning , Thanks a lot for your reply. that code is working well. In that i am having one doubt, what is the use of vbLf ..? And one more doubt: If the cell value starts with '987,'789,'12036,'1364, etc I want to replce this character ( ' ) to space. please help me in this. Thanks in advance. "Dave Peterson" wrote: Modified from a previous post: Option Explicit Sub testme01() Dim FoundCell As Range Dim ConstCells As Range Dim BeforeStr As String Dim AfterStr As String BeforeStr = vbLf AfterStr = " " With ActiveSheet Set ConstCells = Nothing On Error Resume Next Set ConstCells = .Cells.SpecialCells(xlCellTypeConstants, _ xlTextValues) On Error GoTo 0 If ConstCells Is Nothing Then MsgBox "Select some cells in the used range" Exit Sub End If With ConstCells 'get as many as we can in one step .Replace what:=BeforeStr, Replacement:=AfterStr, _ lookat:=xlPart, SearchOrder:=xlByRows Do Set FoundCell = .Cells.Find(what:=BeforeStr, _ after:=.Cells(1), _ LookIn:=xlValues, _ lookat:=xlPart, _ SearchOrder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False) If FoundCell Is Nothing Then 'done, get out! Exit Do End If FoundCell.Value _ = Replace(FoundCell.Value, BeforeStr, AfterStr) Loop End With End With End Sub If you're using xl97, change that Replace() to application.substitute() If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ps. Try it against a copy of your data--just in case! Avadivelan TCS wrote: Hi All, I wrote one macro for replace for ALT+ENTER to SPACE. It's work well. But if the ONE particular cell (like A1) contains more than 200 entires(1234, 4567, 7895,.. etc) means , at that time it will not work, it says "Formula is too long" . Please give a suggesstion in this case. Thanks in advance for your reply. -- Dave Peterson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
want Replace ALT+ENTER to SPACE
vblf = chr(10) = alt-enter
If you're converting a range of text numbers to number numbers, you can get lots all at one time--no need to loop through the cells. Select an empty cell edit|copy select the range to fix edit|paste special|add Record a macro if you need the code. This'll be lots quicker than cycling through the cells. Avadivelan TCS wrote: Hi Dave Peterson, Good Morning , Thanks a lot for your reply. that code is working well. In that i am having one doubt, what is the use of vbLf ..? And one more doubt: If the cell value starts with '987,'789,'12036,'1364, etc I want to replce this character ( ' ) to space. please help me in this. Thanks in advance. "Dave Peterson" wrote: Modified from a previous post: Option Explicit Sub testme01() Dim FoundCell As Range Dim ConstCells As Range Dim BeforeStr As String Dim AfterStr As String BeforeStr = vbLf AfterStr = " " With ActiveSheet Set ConstCells = Nothing On Error Resume Next Set ConstCells = .Cells.SpecialCells(xlCellTypeConstants, _ xlTextValues) On Error GoTo 0 If ConstCells Is Nothing Then MsgBox "Select some cells in the used range" Exit Sub End If With ConstCells 'get as many as we can in one step .Replace what:=BeforeStr, Replacement:=AfterStr, _ lookat:=xlPart, SearchOrder:=xlByRows Do Set FoundCell = .Cells.Find(what:=BeforeStr, _ after:=.Cells(1), _ LookIn:=xlValues, _ lookat:=xlPart, _ SearchOrder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False) If FoundCell Is Nothing Then 'done, get out! Exit Do End If FoundCell.Value _ = Replace(FoundCell.Value, BeforeStr, AfterStr) Loop End With End With End Sub If you're using xl97, change that Replace() to application.substitute() If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ps. Try it against a copy of your data--just in case! Avadivelan TCS wrote: Hi All, I wrote one macro for replace for ALT+ENTER to SPACE. It's work well. But if the ONE particular cell (like A1) contains more than 200 entires(1234, 4567, 7895,.. etc) means , at that time it will not work, it says "Formula is too long" . Please give a suggesstion in this case. Thanks in advance for your reply. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I enter formula for < or numbers. | Excel Worksheet Functions | |||
formula that will go up one space if no value in specified space | Excel Worksheet Functions | |||
shading a rowwhen a time is entered but no shading when 0 is enter | Excel Worksheet Functions | |||
Can I find and replace "white space" in a cell in Excel? | Excel Worksheet Functions | |||
Is there a (non-binary) "if x then enter y" forumula? | Excel Worksheet Functions |