Error Generated from Array formula

[ExcelMonkey]

I want to check to see if a cell has a column width which
is too small relative to the text that is in it. This
normally generates the ##### error. However how do you
search for this as the number of #s vary based on the
width of column vs the text. The code below would not
necessarily find all the occurences of this.

Case Is = "######"

Thanks

 

[ExcelMonkey]

sorry the titel of this thread has nothing to do with the
question. This was not an error generated by an array
formula. Sorry for the confusion.

 

[Bob Phillips]

You can't check for the #### in the value as it is only in its presentation.

You could try something like

activecell.Text=worksheetfunction.rept("#",len(activecell.text))

which will return True if all #

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

On reflection, probably best to check that it is not also just filled with
#. This function does that as well

Function CellOverflow(rng As Range)

With rng
CellOverflow = .Text = WorksheetFunction.Rept("#", Len(.Text)) And _
.Value <> WorksheetFunction.Rept("#", Len(.Text))
End With

End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[ExcelMonkey]

So Bob that works well. I tried to roll into a Select
Case statement where I am looking at other errors. I need
to find a way to include in my Case stmt only when it has
a value in it. Otherwise it will pick up empty cells. I
tried the following below but it does not seem to work.
Is there a way around this?


Private Function CellHasError(rng As Range) As Boolean
Dim ColumnWidthError As Variant

ColumnWidthError = WorksheetFunction.Rept("#", Len
(ActiveCell.Text))


Select Case rng.Text
If ColumnWidthError = "" Then
Case Is
= "#VALUE!", "#DIV/0!", "#NAME?", "#N/A", "#REF!", "#NUM!",
"#NULL!"
CellHasError = True
Else
Case Is
= "#VALUE!", "#DIV/0!", "#NAME?", "#N/A", "#REF!", "#NUM!",
"#NULL!", ColumnWidthError
CellHasError = True
End If
Case Else
CellHasError = False
End Select

End Function

 

[Bob Phillips]

Not really sure what you are trying, but isn't this sufficient (with my
amended code)

Private Function CellHasError(rng As Range) As Boolean

CellHasError = False
If WorksheetFunction.IsError(rng) Then
CellHasError = True
Else
CellHasError = CellOverflow(rng)
End If

End Function

Private Function CellOverflow(rng As Range)

With rng
CellOverflow = .Text = WorksheetFunction.Rept("#", Len(.Text)) And _
.Value <> WorksheetFunction.Rept("#", Len(.Text))
End With

End Function



--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

VBA CODE EXAMPLES

By John Green

This procedure finds cells on a worksheet containing data displayed as
#####...

Sub FindIncorrectDataDisplay()

Dim rng As Range
For Each rng In ActiveSheet.UsedRange
If IsNumeric(rng.Value) And Left(rng.Text, 1) = "#" Then
MsgBox "Column too narrow for " & rng.Address
End If
Next rng

End Sub