select various cells in one column and move to the next column

[Mldeuser]

Hello

I have a problem. I would like to select various cells in one column and then move them to the next column in the same row.

The size of the spreadsheet will change with each use.

Example: the words Balance Forward Appear in cells I11, I23, I34, I36, I42. and so on. They need to be in column J (same cell numbers).

I googled this and could not find a solution that worked.

Any help would be greatly appreciated.

Thank you
Mark

 

[KOKOSEK]

Sub MoveItRight()
Dim cell As Range
For Each cell In [I1:I100]
    if cell.Value = "Balance Forward" then
       cell.offset(0,1).value=cell.value
       cell.value=""  
     end if
Next cell
End Sub

not tested, just typing.....

 

[Fluff]

How about

Sub Mldeuser()
   With Range("I2", Range("I" & Rows.Count))
      .Replace "Balance Forward", True, xlWhole, , False, , False, False
      .SpecialCells(xlConstants, xlLogical).Offset(, 1).Value = "Balance Forward"
      .SpecialCells(xlConstants, xlLogical).ClearContents
   End With
End Sub

 

[Mldeuser]

It works, can it be updated to look at the entire column "I" and move any it finds. The range can be anywhere from 50 lie items to 5000 line items.

Thank you

 

[Mldeuser]

The second macro works perfectly.

Thank you

 

[Fluff]

You're welcome & thanks for the feedback

 

[Mldeuser]

One other thing once the words Balance Forward is moved to column J is it possible to use this to look at column L and if the cell has a value move that value to column M.

 

[Fluff]

Will there be anything else on those rows from col J onwards that needs to remain where it is?

 

[Mldeuser]

Column L does have values in other rows that need to stay in column L. Only the values in column L where column J has "Balance Forward in the same row need to be moved to column M

 

[Fluff]

Yes, but are there values on those rows that need to remain where they are, so for instance if "Balance Forward" is in I15 do you have anything in (say) K15,N15 that need to stay in those cells?