problem determining a cell's value

[ybazizi]

I have an add-in to excel that I have referenced in a formula. The add-in
retrieves a value from a database. When a value is not found, it displays
'No events found.' In an IF THEN ELSE statement, I'm checking for 'No events
found.', but when the cell meets that criteria it is not matching and just
goes to the 'Error' part of my Else statement (see below). My guess is that
my statement is retrieving the formula in the cell, and not the formula's
results. If I do a MSGBOX, I get the results and not the formula. Any
thoughts?

If (IsNumeric(Worksheets("Data").Range("A7").Value)) Then
MsgBox Worksheets("Data").Range("A7").Value 'show the value
ElseIf CStr(Worksheets("Data").Range("A7").Value) = "No events found."
Then
MsgBox "No events found."
Else
MsgBox "Error"
End If

I've tried it with and without the CStr function to see if it was a format
conversion issue to no avail.

Thanks

[Kris]

ybazizi wrote:
> I have an add-in to excel that I have referenced in a formula. The add-in
> retrieves a value from a database. When a value is not found, it displays
> 'No events found.' In an IF THEN ELSE statement, I'm checking for 'No events
> found.', but when the cell meets that criteria it is not matching and just
> goes to the 'Error' part of my Else statement (see below). My guess is that
> my statement is retrieving the formula in the cell, and not the formula's
> results. If I do a MSGBOX, I get the results and not the formula. Any
> thoughts?
>
> If (IsNumeric(Worksheets("Data").Range("A7").Value)) Then
> MsgBox Worksheets("Data").Range("A7").Value 'show the value
> ElseIf CStr(Worksheets("Data").Range("A7").Value) = "No events found."
> Then
> MsgBox "No events found."
> Else
> MsgBox "Error"
> End If
>
> I've tried it with and without the CStr function to see if it was a format
> conversion issue to no avail.
>
> Thanks


Probably add-in doesn't return string but error object visible as a string.
For the same reason you can't compare cell value with "#N/A" or "#DIV/0"
as strings to determine if there is error in the cell

Try to use IsError function.

[Bob Phillips]

My guess is that the cell doesn't contain that exact text, maybe it has a
leading or trailing space.

Do a =LEN(A7) and see if it is 16.

--

HTH

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


"ybazizi" <[email protected]> wrote in message
news:[email protected]...
> I have an add-in to excel that I have referenced in a formula. The add-in
> retrieves a value from a database. When a value is not found, it displays
> 'No events found.' In an IF THEN ELSE statement, I'm checking for 'No
events
> found.', but when the cell meets that criteria it is not matching and just
> goes to the 'Error' part of my Else statement (see below). My guess is
that
> my statement is retrieving the formula in the cell, and not the formula's
> results. If I do a MSGBOX, I get the results and not the formula. Any
> thoughts?
>
> If (IsNumeric(Worksheets("Data").Range("A7").Value)) Then
> MsgBox Worksheets("Data").Range("A7").Value 'show the value
> ElseIf CStr(Worksheets("Data").Range("A7").Value) = "No events found."
> Then
> MsgBox "No events found."
> Else
> MsgBox "Error"
> End If
>
> I've tried it with and without the CStr function to see if it was a format
> conversion issue to no avail.
>
> Thanks

[ybazizi]

That was the trick. A trim took care of the problem.

"Bob Phillips" wrote:

> My guess is that the cell doesn't contain that exact text, maybe it has a
> leading or trailing space.
>
> Do a =LEN(A7) and see if it is 16.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "ybazizi" <[email protected]> wrote in message
> news:[email protected]...
> > I have an add-in to excel that I have referenced in a formula. The add-in
> > retrieves a value from a database. When a value is not found, it displays
> > 'No events found.' In an IF THEN ELSE statement, I'm checking for 'No
> events
> > found.', but when the cell meets that criteria it is not matching and just
> > goes to the 'Error' part of my Else statement (see below). My guess is
> that
> > my statement is retrieving the formula in the cell, and not the formula's
> > results. If I do a MSGBOX, I get the results and not the formula. Any
> > thoughts?
> >
> > If (IsNumeric(Worksheets("Data").Range("A7").Value)) Then
> > MsgBox Worksheets("Data").Range("A7").Value 'show the value
> > ElseIf CStr(Worksheets("Data").Range("A7").Value) = "No events found."
> > Then
> > MsgBox "No events found."
> > Else
> > MsgBox "Error"
> > End If
> >
> > I've tried it with and without the CStr function to see if it was a format
> > conversion issue to no avail.
> >
> > Thanks
>
>
>