VBA for if a cell contains certain number of characters

[Legacy 143009]

Hi Guys!

I am looking for a very simple VBA that moves data (NOT COPY) from one colomn for ex. (K1:K256) to another (G1:G256) if cell contains a certain number of character (for ex.14 haracters).

Thanks a lot!

 

[VoG]

Try

Sub ctest()
Dim i As Long
For i = 1 To 256
    With Range("K" & i)
        If Len(.Value) = 14 Then .Cut Destination:=.Offset(, -4)
    End With
Next i
End Sub

 

[Legacy 143009]

Where is address G in this formula?

Try

Sub ctest()
Dim i As Long
For i = 1 To 256
    With Range("K" & i)
        If Len(.Value) = 14 Then .Cut Destination:=.Offset(, -4)
    End With
Next i
End Sub

Ahaa!! sorry!! Offset-4!

 

[VoG]

In the offset

If Len(.Value) = 14 Then .Cut Destination:=.Offset(, -4)

 

[Legacy 143009]

But somehow it didn't work?! No change on sheet :/

 

[VoG]

It works for me. The length has to be exactly 14 characters.

 

[Legacy 143009]

OK, I will try to make it works correct. What if I had no row limitation and I want entire K colmn?

 

[mumps]

Try:

Sub CharCount()
    Dim cell As Range
    Dim bottomK As Integer
    bottomK = Range("k" & Rows.Count).End(xlUp).Row
    Dim rng As Range
    Set rng = Range("K1:K" & bottomK)
        For Each cell In rng
            If Len(cell) = 14 Then
                cell.Copy cell.Offset(0, -4)
                cell.ClearContents
            End If
        Next cell
End Sub

 

[Legacy 143009]

As you see I have some date cells in K column which are exactly 14 characters but there is no change in the sheet. Is there any other way to trim them from K column to G column?

 

[Legacy 143009]

Solved Thanks! I hadn't create a "macro-enabled" workbook