Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 3 of 3

Thread: VBA: If Cell contains Text, move cell

  1. #1
    Join Date
    25th January 2012
    Posts
    16

    VBA: If Cell contains Text, move cell

    Hi Everyone,

    I'm having a little difficulty figuring out the VBA, I figured a part of it but cant finish it so your help is appreciated.

    What I want to do:
    If a cell contains any text (not numbers), to move the cell to another cell.

    For Example:

    A1: 5
    A2: Dog
    A3: 3

    I want A1 and A3 to stay where they are, however I want A2 to move to B2

    What I have so far:
    VB:
    Range("TextCell").Select 
    Selection.Cut Destination:=Range ("???") 
    
    
    Questions:

    - What will the string be to select the cells with only text and not numbers?
    - To select and cut, how do I move it to the adjacent cell?

    Thanks!
    Last edited by AAE; July 4th, 2012 at 05:59. Reason: add code tags

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    Re: Help with VBA: If Cell contains Text, move cell

    Please use code tags when posting VB code, per the forum rules you agreed to follow.
    I've added the tags for you this time.

    Basic code- there are a number of approaches that could be used, including using a filter to move all cells at once.

    VB:
    If Not IsNumeric(ActiveCell.Value) Then 
        Range("B" & ActiveCell.Row) = ActiveCell.Value 
        ActiveCell.ClearContents 
    End If 
    
    
    or

    VB:
    If Not IsNumeric(ActiveCell.Value) Then 
        ActiveCell.Cut Range("B" & ActiveCell.Row) 
    End If 
    
    
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

  3. #3
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,482

    Re: VBA: If Cell contains Text, move cell

    Something like
    VB:
    Sub test() 
        Dim r As Range 
        For Each r In Range("a1", Range("a" & Rows.Count).End(xlUp)) 
            If r.Value Like "*[A-z]*" Then 
                r.Cut r(, 2) 
            End If 
        Next 
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 7
    Last Post: November 29th, 2011, 10:04
  2. Macro to move text within cell
    By tonywill in forum EXCEL HELP
    Replies: 11
    Last Post: May 3rd, 2011, 02:36
  3. Replies: 3
    Last Post: August 17th, 2010, 06:09
  4. Move Mid Text In Cell To Front. Reverse
    By Nobi in forum EXCEL HELP
    Replies: 27
    Last Post: March 20th, 2008, 08:16
  5. Move part of text in cell to another cell
    By joop in forum EXCEL HELP
    Replies: 3
    Last Post: May 17th, 2006, 02:27

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
  •  
porno