ActiveCell.Offset Question

jeran042 · Feb 5th 2018

Good morning all,

I have a loop that I am having trouble getting to work the way I desire. The purpose of this loop is to look in column "J", and if the value ISTEXT, offset the value 1 column,
However what is happening is that it sees the first cell in the column, and it is in fact text, and it is just offsetting 1 column, not moving the value, or continuing the loop,

Here is what I have so far, any help or guidance is appreciated,

Code

Sub Text_Loop()


      Dim x As Integer
      
      Application.ScreenUpdating = False
      
      ' Set numrows = number of rows of data.
      NumRows = Range("J1", Range("J1").End(xlDown)).Rows.Count
      
      ' Select cell J1.
      Range("J1").Select
      
      ' Establish "For" loop to loop "numrows" number of times.
      For x = 1 To NumRows
        If Application.WorksheetFunction.IsText(ActiveCell.Value) = True Then
         ActiveCell.Offset(0, 1).Value
      End If
      
      Next
      
      Application.ScreenUpdating = True
      
End Sub

Display More

chirayuw · Feb 5th 2018

I don't understand what you're actually trying to do. looking at your code. it technically does nothing. Tthe "Activecell.Offset(0,1).Value" segment doesn't actually populate anything and it cant be used to move anything around.
1) Are you trying to check if current cell is Text?
2) Basis that I assume you actually want to move the value from the current cell into the column on the right of that cell?
3) Is it just value in current cell you want to move to right or you want the row to move. i.e. if current column is J and the value is text then move the current cell as well as any cells on the right, 1 cell to the right?

jeran042 · Feb 5th 2018

Correct, I am trying to check if the active cell ISTEXT, and if it is, move that cell over one column to K, and loop until I come to the first blank cell

**Carim** · Feb 5th 2018

Hello,

You could test the following

Code

Sub Text_Loop()
Dim x As Integer
Dim NumRows As Long


      Application.ScreenUpdating = False
      ' Set numrows = number of rows of data.
      NumRows = Range("J1", Range("J1").End(xlDown)).Rows.Count
      ' Establish "For" loop to loop "numrows" number of times.
      For x = 1 To NumRows
        If Application.WorksheetFunction.IsText(Range("J" & x)) = True Then
          Range("J" & x).Cut Destination:=Range("K" & x)
        End If
      Next x
      Application.ScreenUpdating = True
  
End Sub

Display More

Hope this will help

chirayuw · Feb 5th 2018

Modified your code to do what you want. I wasn't sure if you also wanted to clear the activecell after the data has been moved over so I've added that bit of code in. Remove the "ActiveCell.ClearContents" argument if u don't want that to happen.

Code

Sub Text_Loop()


Application.ScreenUpdating = False


Dim x As Integer


' Set numrows = number of rows of data.
NumRows = Range("J1", Range("J1").End(xlDown)).Rows.Count


' Select cell J1.
Range("J1").Select
     
' Establish "For" loop to loop "numrows" number of times.
For x = 1 To NumRows
    If Application.WorksheetFunction.IsText(ActiveCell) = True Then
        ActiveCell.Offset(0, 1) = ActiveCell
        ActiveCell.ClearContents
    End If
        ActiveCell.Offset(1, 0).Select
Next
 
Application.ScreenUpdating = True
     
End Sub

Display More

jeran042 · Feb 5th 2018

Carim & chirayuw ,
Thank you both for your time and help, Both of these solutions do what I need.

**Carim** · Feb 5th 2018

Quote from jeran042

Carim & chirayuw ,
Thank you both for your time and help, Both of these solutions do what I need.

Glad you could fix your problem ...:wink:

When using a macro ... you should avoid all the Select and Activate ... which are not necessary ... and which slow down the macro execution ...

Thanks ... for your Thanks ...

ActiveCell.Offset Question

Participate now!

Share