Announcement

Collapse
No announcement yet.

VBA: If Cell contains Text, move cell

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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:
    Code:
    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, 05:59. Reason: add code tags

  • #2
    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.

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

    Code:
    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

    Comment


    • #3


      Re: VBA: If Cell contains Text, move cell

      Something like
      Code:
      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

      Comment

      Working...
      X