Error Generated from Array formula

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote
in message
>news:[email protected]...
>> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote
> in message
> >news:[email protected]...
> >> 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" <[email protected]> wrote in message
news:[email protected]...
> 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
> >.
> >