Data Validation Macro
On Dec 5, 6:57 pm, Dave Peterson wrote:
It's not the len() function that's causing the trouble.
It's that the cell contains an error.
This'll cause the same problem:
msgbox cell.value
If that cell contains an error.
You could code around it:
For Each cell In Target.cells
if iserror(cell.value) then
'skip it
else
if cell.hasformula then
'skip it???
else
If Len(cell.value) 255 Then
ectr = ectr + 1
cell.Value = Left(cell.value, 255)
End if
End If
end if
Next cell
wrote:
Thank you. Works like a charm.
Just learning VBA, would you mind explaining to me why the len
function freezes up on seeing a formula? When I try it in excel it
returns the length of the result of the formula. When the eq has an
error it reproduces that error value which I could see freezing the
macro. In this case, the sheet does not have any errors displayed in
the formulas that are present.
Thanks again.
John
--
Dave Peterson
Thanks for the clarification.
John
|