Results 1 to 5 of 5

VBA to account for alt+enter in a macro

Threaded View

eirwin1 VBA to account for alt+enter... 02-02-2015, 03:23 PM
Pauleyb Re: VBA to account for... 02-02-2015, 05:40 PM
eirwin1 Re: VBA to account for... 02-03-2015, 03:28 PM
Pauleyb Re: VBA to account for... 02-03-2015, 04:04 PM
eirwin1 Re: VBA to account for... 02-25-2015, 11:33 AM
  1. #1
    Registered User
    Join Date
    01-15-2014
    Location
    Calgary, Alberta
    MS-Off Ver
    Excel 2010
    Posts
    5

    VBA to account for alt+enter in a macro

    We have created a macro that will enable us to move text within a cell over two spaces. It works perfectly but only for the first line of text. If we have two lines of text in one cell, thus having used Alt+enter to type a second line in a cell, this macro doesn't move the second line of text over the two spaces. Therefore the macro moves the first line over the two spaces but leaves the second line to the left of the cell. Can anyone help me with figuring out what I can add to this macro to move the second line over as well. My macro is posted below. Thank you.

    Sub Space()
    Dim cell As Range, rngCol As Range
    ' Space Macro
    ' To add Space
    '
    ' Keyboard Shortcut: Ctrl+s
        Application.ScreenUpdating = False
    
        On Error Resume Next
        Set rngCol = ActiveCell.EntireColumn.SpecialCells(xlCellTypeConstants)
        On Error GoTo 0
    
        If Not rngCol Is Nothing Then
            For Each cell In rngCol
                If InStr(1, cell.Value, "  ") = 0 Then        'exclude cells already with double spaces
                    If IsNumeric(cell.Value) Then
                        cell.Value = "'  " & cell.Value        'for numbers
                    Else
                        cell.Value = "  " & cell.Value        'for text
                    End If
                End If
            Next cell
        End If
    
        Application.ScreenUpdating = True
    
    End Sub
    Last edited by eirwin1; 02-02-2015 at 03:36 PM. Reason: forgot code tags around macro

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Remove an account range form a column with account numbers.
    By kokapelly in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 09-09-2013, 11:34 AM
  2. [SOLVED] How to censor account numbers based on how many characters are in the account number
    By Mcorydon in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-25-2013, 01:06 PM
  3. Replies: 1
    Last Post: 09-07-2012, 12:00 PM
  4. Replies: 9
    Last Post: 03-12-2012, 05:30 AM
  5. Replies: 1
    Last Post: 08-04-2011, 06:17 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1