Error in code
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 |
Error in code
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 |
Error in code
' 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 |
Error in code
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 |
Error in code
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 |
All times are GMT +1. The time now is 05:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com