Error in code

[Guest]

The purpose of this code is to scan the entire sheet and move all data
greater than 7 characters to Col H to column I. I get an error in the If
Len statement. Any ideas?

' If text great than 7 characters in column H move text to to I Column

Set rng = Range(Cells(2, "H"), Cells(Rows.Count, "H").End(xlUp))
For Each cell In rng
If Len(Cells.Value) > 7 Then
cell.Offset(0, 1).Value = cell.Value
cell.ClearContents
End If
Next

 

[Guest]

You might want to try VBA.Len instead of just Len.

 

[Bob Phillips]

' If text great than 7 characters in column H move text to to I Column

Set rng = Range(Cells(2, "H"), Cells(Rows.Count, "H").End(xlUp))
For Each Cell In rng
If Len(Cell.Value) > 7 Then
Cell.Offset(0, 1).Value = Cell.Value
Cell.ClearContents
End If
Next

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Ron de Bruin]

Typo

If Len(Cells.Value) > 7 Then

remove the s
Cell.Value

 

[Tom Ogilvy]

did you check against the original:

Sub MoveValues()
Dim rng as Range, cell as Range
set rng = Range(cells(2,"H"),cells(rows.count,"H").end(xlup))
for each cell in rng
if len(cell) = 13 then
cell.offset(0,1).value = cell.value
cell.clearcontents
end if
next
End sub