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

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.


-----Original Message-----
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
.

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

--
Regards,
Tom Ogivly

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


-----Original Message-----
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
.

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(act ivecell.text))

which will return True if all #

--

HTH

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


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

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)


"Bob Phillips" wrote in message
...
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(act ivecell.text))

which will return True if all #

--

HTH

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


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

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




-----Original Message-----
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)


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


.

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)


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




-----Original Message-----
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)


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


.