error msg in function .... any ideas????

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kelvin - 09 Jan 2008 10:31 GMT

Sub RIRthreedates()
Range("m3").Formula = "=IF(count(i3:k3)<3,"",if(i3>j3,i3,if(j3>k3,
j3,k3)))" End Sub

This subR causes a runtime error " application defined or object defined
error" 1004
If I put the formula in the cell directly , it works fine.

if I use the equation Range("m2").Formula = "=counta(a3:a5)" in the
subR
it works fine.

why would the above line cause this error. Can someone shed some light????

thanks

Signature

KWB

Reply to this Message

Pete_UK - 09 Jan 2008 10:58 GMT

Put the EndSub on a separate line.

Pete

> Sub RIRthreedates()
> Range("m3").Formula = "=IF(count(i3:k3)<3,"",if(i3>j3,i3,if(j3>k3,
[quoted text clipped - 14 lines]
> --
> KWB

Reply to this Message

Dave Peterson - 09 Jan 2008 14:48 GMT

You have to double up the double quotes in the string:

Sub RIRthreedates()
Range("m3").Formula _
= "=IF(count(i3:k3)<3,"""",if(i3>j3,i3,if(j3>k3,j3,k3)))"
End Sub

> Sub RIRthreedates()
> Range("m3").Formula = "=IF(count(i3:k3)<3,"",if(i3>j3,i3,if(j3>k3,
[quoted text clipped - 14 lines]
> --
> KWB

Signature

Dave Peterson

Reply to this Message

Kelvin - 09 Jan 2008 16:43 GMT

Dave,

Thanks for the tip. The doubling up on the quotes worked.

Could you enlighten me as to why the four quotes are neccessary?

Thanks to all for your input.

Signature

KWB

> You have to double up the double quotes in the string:
>
[quoted text clipped - 21 lines]
> > --
> > KWB

Reply to this Message

JP - 09 Jan 2008 17:11 GMT

When you actually want quotes to appear, you have to, put them in
quotes. :-)

HTH,
JP

> Dave,
>
[quoted text clipped - 6 lines]
> --
> KWB

Reply to this Message

Kelvin - 09 Jan 2008 18:26 GMT

JP,
Actually what I was looking for, and what actually happens is that the ""
gives me a blank cell if the condition is not met.

I wasn't sure why I would need the quotes around my quotes for this?

Signature

KWB

> When you actually want quotes to appear, you have to, put them in
> quotes. :-)
[quoted text clipped - 12 lines]
> > --
> > KWB

Reply to this Message

Tyro - 09 Jan 2008 17:17 GMT

In Visual Basic text begins and ends with quotes. To express "", you must
put quotes around it. Thus """".

Tyro

> Dave,
>
[quoted text clipped - 32 lines]
>> > --
>> > KWB

Reply to this Message

Kelvin - 11 Jan 2008 06:37 GMT

Thanks to all for the information.

Greatly appreciated.

Kelvin

Signature

KWB

> In Visual Basic text begins and ends with quotes. To express "", you must
> put quotes around it. Thus """".
[quoted text clipped - 37 lines]
> >> > --
> >> > KWB

Reply to this Message