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

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

"JOUIOUI" wrote:

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

' 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)

"JOUIOUI" wrote in message
...
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

Typo

If Len(Cells.Value) 7 Then

remove the s
Cell.Value

--
Regards Ron De Bruin
http://www.rondebruin.nl



"JOUIOUI" wrote in message ...
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

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


--
Regards,
Tom Ogilvy

"JOUIOUI" wrote in message
...
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