if & and code problem

I have spent hours trying to correct this Excel code and am considering
resigning.
Can anyone help ?

ActiveCell.Formula =
"=IF((C2)="""",""1"",IF(AND((A2)=""enddate"",(E2)<>""01/01/01"",(E2)<NOW()+14),""2"",if(and((A2)=""startem"",date(0,month(e2),day(e2))>date(0,month(now()),day(now())),(e2)=""3""),""XXX""))"

I answered this in the other thread when you asked it yesterday.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"jocker" <[email protected]> wrote in message
news:[email protected]...
> I have spent hours trying to correct this Excel code and am considering
> resigning.
> Can anyone help ?
>
> ActiveCell.Formula =
>
"=IF((C2)="""",""1"",IF(AND((A2)=""enddate"",(E2)<>""01/01/01"",(E2)<NOW()+1
4),""2"",if(and((A2)=""startem"",date(0,month(e2),day(e2))>date(0,month(now(
)),day(now())),(e2)=""3""),""XXX""))"
>
>

Sorry, Bob. I did see your reply as to how to do it, but not on why my
expanded code fails.Take pity on a pensioner trying to help his daughter.


"Bob Phillips" <[email protected]> wrote in message
news:%[email protected]...
>I answered this in the other thread when you asked it yesterday.
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "jocker" <[email protected]> wrote in message
> news:[email protected]...
>> I have spent hours trying to correct this Excel code and am considering
>> resigning.
>> Can anyone help ?
>>
>> ActiveCell.Formula =
>>
> "=IF((C2)="""",""1"",IF(AND((A2)=""enddate"",(E2)<>""01/01/01"",(E2)<NOW()+1
> 4),""2"",if(and((A2)=""startem"",date(0,month(e2),day(e2))>date(0,month(now(
> )),day(now())),(e2)=""3""),""XXX""))"
>>
>>
>
>

It fails because it is syntactically incorrect. You have brackets in the
wrong place.

This works, or at least doesn't throw an error

Dim sFormula As String

sFormula = _
"=IF(C2="""",""1"",IF(AND(A2=""enddate"",E2<>""01/01/01"",E2<NOW()+14),""2""
,if(and(A2=""startem"",Date(0,Month(E2),Day(E2))>Date(0,Month(Now()),Day(Now
()))),E2=""3"",""XXX"")))"
ActiveCell.Formula = sFormula

The way I checked it was to take the string you were trying to use, display
that in the immediate window using the print method (? in the immediate
window), and then take the displayed value and stick it in a cell. Much
easier to see the problem then.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"jocker" <[email protected]> wrote in message
news:[email protected]...
> Sorry, Bob. I did see your reply as to how to do it, but not on why my
> expanded code fails.Take pity on a pensioner trying to help his daughter.
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:%[email protected]...
> >I answered this in the other thread when you asked it yesterday.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "jocker" <[email protected]> wrote in message
> > news:[email protected]...
> >> I have spent hours trying to correct this Excel code and am considering
> >> resigning.
> >> Can anyone help ?
> >>
> >> ActiveCell.Formula =
> >>
> >
"=IF((C2)="""",""1"",IF(AND((A2)=""enddate"",(E2)<>""01/01/01"",(E2)<NOW()+1
> >
4),""2"",if(and((A2)=""startem"",date(0,month(e2),day(e2))>date(0,month(now(
> > )),day(now())),(e2)=""3""),""XXX""))"
> >>
> >>
> >
> >
>
>

Many thanks, Bob. Can u explain in more detail how you found the fault.

Not sure about "print method"

jeff
+++++++++++++++++++++++++++++++++++++
"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> It fails because it is syntactically incorrect. You have brackets in the
> wrong place.
>
> This works, or at least doesn't throw an error
>
> Dim sFormula As String
>
> sFormula = _
> "=IF(C2="""",""1"",IF(AND(A2=""enddate"",E2<>""01/01/01"",E2<NOW()+14),""2""
> ,if(and(A2=""startem"",Date(0,Month(E2),Day(E2))>Date(0,Month(Now()),Day(Now
> ()))),E2=""3"",""XXX"")))"
> ActiveCell.Formula = sFormula
>
> The way I checked it was to take the string you were trying to use,
> display
> that in the immediate window using the print method (? in the immediate
> window), and then take the displayed value and stick it in a cell. Much
> easier to see the problem then.
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "jocker" <[email protected]> wrote in message
> news:[email protected]...
>> Sorry, Bob. I did see your reply as to how to do it, but not on why my
>> expanded code fails.Take pity on a pensioner trying to help his daughter.
>>
>>
>> "Bob Phillips" <[email protected]> wrote in message
>> news:%[email protected]...
>> >I answered this in the other thread when you asked it yesterday.
>> >
>> > --
>> > HTH
>> >
>> > Bob Phillips
>> >
>> > (remove nothere from email address if mailing direct)
>> >
>> > "jocker" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> I have spent hours trying to correct this Excel code and am
>> >> considering
>> >> resigning.
>> >> Can anyone help ?
>> >>
>> >> ActiveCell.Formula =
>> >>
>> >
> "=IF((C2)="""",""1"",IF(AND((A2)=""enddate"",(E2)<>""01/01/01"",(E2)<NOW()+1
>> >
> 4),""2"",if(and((A2)=""startem"",date(0,month(e2),day(e2))>date(0,month(now(
>> > )),day(now())),(e2)=""3""),""XXX""))"
>> >>
>> >>
>> >
>> >
>>
>>
>
>

Okay, I'll try.

Your original formula was

ActiveCell.Formula =
"=IF((C2)="""",""1"",IF(AND((A2)=""enddate"",(E2)<>""01/01/01"",(E2)<NOW()+1
4),""2"",if(and((A2)=""startem"",date(0,month(e2),day(e2))>date(0,month(now(
)),day(now())),(e2)=""3""),""XXX""))"

I took this part and in the immediate window in the VBIDE (Ctrl-G) I did

?"=IF((C2)="""",""1"",IF(AND((A2)=""enddate"",(E2)<>""01/01/01"",(E2)<NOW()+
14),""2"",if(and((A2)=""startem"",date(0,month(e2),day(e2))>date(0,month(now
()),day(now())),(e2)=""3""),""XXX""))"

the ? is print. This gave a result of

=IF((C2)="","1",IF(AND((A2)="enddate",(E2)<>"01/01/01",(E2)<NOW()+14),"2",if
(and((A2)="startem",date(0,month(e2),day(e2))>date(0,month(now()),day(now())
),(e2)="3"),"XXX"))


which I could copy, paste ina worksheet ecll, and see the problem more
easily.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"jocker" <[email protected]> wrote in message
news:[email protected]...
> Many thanks, Bob. Can u explain in more detail how you found the fault.
>
> Not sure about "print method"
>
> jeff
> +++++++++++++++++++++++++++++++++++++
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > It fails because it is syntactically incorrect. You have brackets in the
> > wrong place.
> >
> > This works, or at least doesn't throw an error
> >
> > Dim sFormula As String
> >
> > sFormula = _
> >
"=IF(C2="""",""1"",IF(AND(A2=""enddate"",E2<>""01/01/01"",E2<NOW()+14),""2""
> >
,if(and(A2=""startem"",Date(0,Month(E2),Day(E2))>Date(0,Month(Now()),Day(Now
> > ()))),E2=""3"",""XXX"")))"
> > ActiveCell.Formula = sFormula
> >
> > The way I checked it was to take the string you were trying to use,
> > display
> > that in the immediate window using the print method (? in the immediate
> > window), and then take the displayed value and stick it in a cell. Much
> > easier to see the problem then.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "jocker" <[email protected]> wrote in message
> > news:[email protected]...
> >> Sorry, Bob. I did see your reply as to how to do it, but not on why my
> >> expanded code fails.Take pity on a pensioner trying to help his
daughter.
> >>
> >>
> >> "Bob Phillips" <[email protected]> wrote in message
> >> news:%[email protected]...
> >> >I answered this in the other thread when you asked it yesterday.
> >> >
> >> > --
> >> > HTH
> >> >
> >> > Bob Phillips
> >> >
> >> > (remove nothere from email address if mailing direct)
> >> >
> >> > "jocker" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> >> I have spent hours trying to correct this Excel code and am
> >> >> considering
> >> >> resigning.
> >> >> Can anyone help ?
> >> >>
> >> >> ActiveCell.Formula =
> >> >>
> >> >
> >
"=IF((C2)="""",""1"",IF(AND((A2)=""enddate"",(E2)<>""01/01/01"",(E2)<NOW()+1
> >> >
> >
4),""2"",if(and((A2)=""startem"",date(0,month(e2),day(e2))>date(0,month(now(
> >> > )),day(now())),(e2)=""3""),""XXX""))"
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

That's what I did but on hitting the Enter key all I get is "compile error"
and nothing in the Intermediate window changes

++++++++++++++++


"Bob Phillips" <[email protected]> wrote in message
news:%[email protected]...
> Okay, I'll try.
>
> Your original formula was
>
> ActiveCell.Formula =
> "=IF((C2)="""",""1"",IF(AND((A2)=""enddate"",(E2)<>""01/01/01"",(E2)<NOW()+1
> 4),""2"",if(and((A2)=""startem"",date(0,month(e2),day(e2))>date(0,month(now(
> )),day(now())),(e2)=""3""),""XXX""))"
>
> I took this part and in the immediate window in the VBIDE (Ctrl-G) I did
>
> ?"=IF((C2)="""",""1"",IF(AND((A2)=""enddate"",(E2)<>""01/01/01"",(E2)<NOW()+
> 14),""2"",if(and((A2)=""startem"",date(0,month(e2),day(e2))>date(0,month(now
> ()),day(now())),(e2)=""3""),""XXX""))"
>
> the ? is print. This gave a result of
>
> =IF((C2)="","1",IF(AND((A2)="enddate",(E2)<>"01/01/01",(E2)<NOW()+14),"2",if
> (and((A2)="startem",date(0,month(e2),day(e2))>date(0,month(now()),day(now())
> ),(e2)="3"),"XXX"))
>
>
> which I could copy, paste ina worksheet ecll, and see the problem more
> easily.
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "jocker" <[email protected]> wrote in message
> news:[email protected]...
>> Many thanks, Bob. Can u explain in more detail how you found the fault.
>>
>> Not sure about "print method"
>>
>> jeff
>> +++++++++++++++++++++++++++++++++++++
>> "Bob Phillips" <[email protected]> wrote in message
>> news:[email protected]...
>> > It fails because it is syntactically incorrect. You have brackets in
>> > the
>> > wrong place.
>> >
>> > This works, or at least doesn't throw an error
>> >
>> > Dim sFormula As String
>> >
>> > sFormula = _
>> >
> "=IF(C2="""",""1"",IF(AND(A2=""enddate"",E2<>""01/01/01"",E2<NOW()+14),""2""
>> >
> ,if(and(A2=""startem"",Date(0,Month(E2),Day(E2))>Date(0,Month(Now()),Day(Now
>> > ()))),E2=""3"",""XXX"")))"
>> > ActiveCell.Formula = sFormula
>> >
>> > The way I checked it was to take the string you were trying to use,
>> > display
>> > that in the immediate window using the print method (? in the immediate
>> > window), and then take the displayed value and stick it in a cell. Much
>> > easier to see the problem then.
>> >
>> > --
>> > HTH
>> >
>> > Bob Phillips
>> >
>> > (remove nothere from email address if mailing direct)
>> >
>> > "jocker" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> Sorry, Bob. I did see your reply as to how to do it, but not on why my
>> >> expanded code fails.Take pity on a pensioner trying to help his
> daughter.
>> >>
>> >>
>> >> "Bob Phillips" <[email protected]> wrote in message
>> >> news:%[email protected]...
>> >> >I answered this in the other thread when you asked it yesterday.
>> >> >
>> >> > --
>> >> > HTH
>> >> >
>> >> > Bob Phillips
>> >> >
>> >> > (remove nothere from email address if mailing direct)
>> >> >
>> >> > "jocker" <[email protected]> wrote in message
>> >> > news:[email protected]...
>> >> >> I have spent hours trying to correct this Excel code and am
>> >> >> considering
>> >> >> resigning.
>> >> >> Can anyone help ?
>> >> >>
>> >> >> ActiveCell.Formula =
>> >> >>
>> >> >
>> >
> "=IF((C2)="""",""1"",IF(AND((A2)=""enddate"",(E2)<>""01/01/01"",(E2)<NOW()+1
>> >> >
>> >
> 4),""2"",if(and((A2)=""startem"",date(0,month(e2),day(e2))>date(0,month(now(
>> >> > )),day(now())),(e2)=""3""),""XXX""))"
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

You shouldn't get a compile error as you are just printing a string. You did
only copy the parts between the first and last quotes, not the Activecell
bit as well?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"jocker" <[email protected]> wrote in message
news:[email protected]...
> That's what I did but on hitting the Enter key all I get is "compile
error"
> and nothing in the Intermediate window changes
>
> ++++++++++++++++
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:%[email protected]...
> > Okay, I'll try.
> >
> > Your original formula was
> >
> > ActiveCell.Formula =
> >
"=IF((C2)="""",""1"",IF(AND((A2)=""enddate"",(E2)<>""01/01/01"",(E2)<NOW()+1
> >
4),""2"",if(and((A2)=""startem"",date(0,month(e2),day(e2))>date(0,month(now(
> > )),day(now())),(e2)=""3""),""XXX""))"
> >
> > I took this part and in the immediate window in the VBIDE (Ctrl-G) I did
> >
> >
?"=IF((C2)="""",""1"",IF(AND((A2)=""enddate"",(E2)<>""01/01/01"",(E2)<NOW()+
> >
14),""2"",if(and((A2)=""startem"",date(0,month(e2),day(e2))>date(0,month(now
> > ()),day(now())),(e2)=""3""),""XXX""))"
> >
> > the ? is print. This gave a result of
> >
> >
=IF((C2)="","1",IF(AND((A2)="enddate",(E2)<>"01/01/01",(E2)<NOW()+14),"2",if
> >
(and((A2)="startem",date(0,month(e2),day(e2))>date(0,month(now()),day(now())
> > ),(e2)="3"),"XXX"))
> >
> >
> > which I could copy, paste ina worksheet ecll, and see the problem more
> > easily.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "jocker" <[email protected]> wrote in message
> > news:[email protected]...
> >> Many thanks, Bob. Can u explain in more detail how you found the fault.
> >>
> >> Not sure about "print method"
> >>
> >> jeff
> >> +++++++++++++++++++++++++++++++++++++
> >> "Bob Phillips" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > It fails because it is syntactically incorrect. You have brackets in
> >> > the
> >> > wrong place.
> >> >
> >> > This works, or at least doesn't throw an error
> >> >
> >> > Dim sFormula As String
> >> >
> >> > sFormula = _
> >> >
> >
"=IF(C2="""",""1"",IF(AND(A2=""enddate"",E2<>""01/01/01"",E2<NOW()+14),""2""
> >> >
> >
,if(and(A2=""startem"",Date(0,Month(E2),Day(E2))>Date(0,Month(Now()),Day(Now
> >> > ()))),E2=""3"",""XXX"")))"
> >> > ActiveCell.Formula = sFormula
> >> >
> >> > The way I checked it was to take the string you were trying to use,
> >> > display
> >> > that in the immediate window using the print method (? in the
immediate
> >> > window), and then take the displayed value and stick it in a cell.
Much
> >> > easier to see the problem then.
> >> >
> >> > --
> >> > HTH
> >> >
> >> > Bob Phillips
> >> >
> >> > (remove nothere from email address if mailing direct)
> >> >
> >> > "jocker" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> >> Sorry, Bob. I did see your reply as to how to do it, but not on why
my
> >> >> expanded code fails.Take pity on a pensioner trying to help his
> > daughter.
> >> >>
> >> >>
> >> >> "Bob Phillips" <[email protected]> wrote in message
> >> >> news:%[email protected]...
> >> >> >I answered this in the other thread when you asked it yesterday.
> >> >> >
> >> >> > --
> >> >> > HTH
> >> >> >
> >> >> > Bob Phillips
> >> >> >
> >> >> > (remove nothere from email address if mailing direct)
> >> >> >
> >> >> > "jocker" <[email protected]> wrote in message
> >> >> > news:[email protected]...
> >> >> >> I have spent hours trying to correct this Excel code and am
> >> >> >> considering
> >> >> >> resigning.
> >> >> >> Can anyone help ?
> >> >> >>
> >> >> >> ActiveCell.Formula =
> >> >> >>
> >> >> >
> >> >
> >
"=IF((C2)="""",""1"",IF(AND((A2)=""enddate"",(E2)<>""01/01/01"",(E2)<NOW()+1
> >> >> >
> >> >
> >
4),""2"",if(and((A2)=""startem"",date(0,month(e2),day(e2))>date(0,month(now(
> >> >> > )),day(now())),(e2)=""3""),""XXX""))"
> >> >> >>
> >> >> >>
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

Yes, I entered it without the "Activecell" phrase. Even when I copy & paste
the code you put in the Immediate window, I get "Compile Error. Expected:
Line number or label or statement or end of statement

Sorry to bother you again
+++++++++++++


"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> You shouldn't get a compile error as you are just printing a string. You
> did
> only copy the parts between the first and last quotes, not the Activecell
> bit as well?
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "jocker" <[email protected]> wrote in message
> news:[email protected]...
>> That's what I did but on hitting the Enter key all I get is "compile
> error"
>> and nothing in the Intermediate window changes
>>
>> ++++++++++++++++
>>
>>
>> "Bob Phillips" <[email protected]> wrote in message
>> news:%[email protected]...
>> > Okay, I'll try.
>> >
>> > Your original formula was
>> >
>> > ActiveCell.Formula =
>> >
> "=IF((C2)="""",""1"",IF(AND((A2)=""enddate"",(E2)<>""01/01/01"",(E2)<NOW()+1
>> >
> 4),""2"",if(and((A2)=""startem"",date(0,month(e2),day(e2))>date(0,month(now(
>> > )),day(now())),(e2)=""3""),""XXX""))"
>> >
>> > I took this part and in the immediate window in the VBIDE (Ctrl-G) I
>> > did
>> >
>> >
> ?"=IF((C2)="""",""1"",IF(AND((A2)=""enddate"",(E2)<>""01/01/01"",(E2)<NOW()+
>> >
> 14),""2"",if(and((A2)=""startem"",date(0,month(e2),day(e2))>date(0,month(now
>> > ()),day(now())),(e2)=""3""),""XXX""))"
>> >
>> > the ? is print. This gave a result of
>> >
>> >
> =IF((C2)="","1",IF(AND((A2)="enddate",(E2)<>"01/01/01",(E2)<NOW()+14),"2",if
>> >
> (and((A2)="startem",date(0,month(e2),day(e2))>date(0,month(now()),day(now())
>> > ),(e2)="3"),"XXX"))
>> >
>> >
>> > which I could copy, paste ina worksheet ecll, and see the problem more
>> > easily.
>> >
>> > --
>> > HTH
>> >
>> > Bob Phillips
>> >
>> > (remove nothere from email address if mailing direct)
>> >
>> > "jocker" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> Many thanks, Bob. Can u explain in more detail how you found the
>> >> fault.
>> >>
>> >> Not sure about "print method"
>> >>
>> >> jeff
>> >> +++++++++++++++++++++++++++++++++++++
>> >> "Bob Phillips" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > It fails because it is syntactically incorrect. You have brackets in
>> >> > the
>> >> > wrong place.
>> >> >
>> >> > This works, or at least doesn't throw an error
>> >> >
>> >> > Dim sFormula As String
>> >> >
>> >> > sFormula = _
>> >> >
>> >
> "=IF(C2="""",""1"",IF(AND(A2=""enddate"",E2<>""01/01/01"",E2<NOW()+14),""2""
>> >> >
>> >
> ,if(and(A2=""startem"",Date(0,Month(E2),Day(E2))>Date(0,Month(Now()),Day(Now
>> >> > ()))),E2=""3"",""XXX"")))"
>> >> > ActiveCell.Formula = sFormula
>> >> >
>> >> > The way I checked it was to take the string you were trying to use,
>> >> > display
>> >> > that in the immediate window using the print method (? in the
> immediate
>> >> > window), and then take the displayed value and stick it in a cell.
> Much
>> >> > easier to see the problem then.
>> >> >
>> >> > --
>> >> > HTH
>> >> >
>> >> > Bob Phillips
>> >> >
>> >> > (remove nothere from email address if mailing direct)
>> >> >
>> >> > "jocker" <[email protected]> wrote in message
>> >> > news:[email protected]...
>> >> >> Sorry, Bob. I did see your reply as to how to do it, but not on why
> my
>> >> >> expanded code fails.Take pity on a pensioner trying to help his
>> > daughter.
>> >> >>
>> >> >>
>> >> >> "Bob Phillips" <[email protected]> wrote in message
>> >> >> news:%[email protected]...
>> >> >> >I answered this in the other thread when you asked it yesterday.
>> >> >> >
>> >> >> > --
>> >> >> > HTH
>> >> >> >
>> >> >> > Bob Phillips
>> >> >> >
>> >> >> > (remove nothere from email address if mailing direct)
>> >> >> >
>> >> >> > "jocker" <[email protected]> wrote in message
>> >> >> > news:[email protected]...
>> >> >> >> I have spent hours trying to correct this Excel code and am
>> >> >> >> considering
>> >> >> >> resigning.
>> >> >> >> Can anyone help ?
>> >> >> >>
>> >> >> >> ActiveCell.Formula =
>> >> >> >>
>> >> >> >
>> >> >
>> >
> "=IF((C2)="""",""1"",IF(AND((A2)=""enddate"",(E2)<>""01/01/01"",(E2)<NOW()+1
>> >> >> >
>> >> >
>> >
> 4),""2"",if(and((A2)=""startem"",date(0,month(e2),day(e2))>date(0,month(now(
>> >> >> > )),day(now())),(e2)=""3""),""XXX""))"
>> >> >> >>
>> >> >> >>
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

Ron,

I think the problem is arising from copying from the NG getting linefeeds
embedded. This

?"=IF((C2)="""",""1"",IF(AND((A2)=""enddate"",(E2)<>""01/01/01"",(E2)<NOW()+
14),""2"",if(and((A2)=""startem"",date(0,month(e2),day(e2))>date(0,month(now
()),day(now())),(e2)=""3""),""XXX""))"

has a CR after the + and the now on the second line. Ensure that you remove
them so that the string is contiguous, it should work okay then.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Ron Dean" <[email protected]> wrote in message
news:[email protected]...
> Yes, I entered it without the "Activecell" phrase. Even when I copy &
paste
> the code you put in the Immediate window, I get "Compile Error. Expected:
> Line number or label or statement or end of statement
>
> Sorry to bother you again
> +++++++++++++
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > You shouldn't get a compile error as you are just printing a string. You
> > did
> > only copy the parts between the first and last quotes, not the
Activecell
> > bit as well?
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "jocker" <[email protected]> wrote in message
> > news:[email protected]...
> >> That's what I did but on hitting the Enter key all I get is "compile
> > error"
> >> and nothing in the Intermediate window changes
> >>
> >> ++++++++++++++++
> >>
> >>
> >> "Bob Phillips" <[email protected]> wrote in message
> >> news:%[email protected]...
> >> > Okay, I'll try.
> >> >
> >> > Your original formula was
> >> >
> >> > ActiveCell.Formula =
> >> >
> >
"=IF((C2)="""",""1"",IF(AND((A2)=""enddate"",(E2)<>""01/01/01"",(E2)<NOW()+1
> >> >
> >
4),""2"",if(and((A2)=""startem"",date(0,month(e2),day(e2))>date(0,month(now(
> >> > )),day(now())),(e2)=""3""),""XXX""))"
> >> >
> >> > I took this part and in the immediate window in the VBIDE (Ctrl-G) I
> >> > did
> >> >
> >> >
> >
?"=IF((C2)="""",""1"",IF(AND((A2)=""enddate"",(E2)<>""01/01/01"",(E2)<NOW()+
> >> >
> >
14),""2"",if(and((A2)=""startem"",date(0,month(e2),day(e2))>date(0,month(now
> >> > ()),day(now())),(e2)=""3""),""XXX""))"
> >> >
> >> > the ? is print. This gave a result of
> >> >
> >> >
> >
=IF((C2)="","1",IF(AND((A2)="enddate",(E2)<>"01/01/01",(E2)<NOW()+14),"2",if
> >> >
> >
(and((A2)="startem",date(0,month(e2),day(e2))>date(0,month(now()),day(now())
> >> > ),(e2)="3"),"XXX"))
> >> >
> >> >
> >> > which I could copy, paste ina worksheet ecll, and see the problem
more
> >> > easily.
> >> >
> >> > --
> >> > HTH
> >> >
> >> > Bob Phillips
> >> >
> >> > (remove nothere from email address if mailing direct)
> >> >
> >> > "jocker" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> >> Many thanks, Bob. Can u explain in more detail how you found the
> >> >> fault.
> >> >>
> >> >> Not sure about "print method"
> >> >>
> >> >> jeff
> >> >> +++++++++++++++++++++++++++++++++++++
> >> >> "Bob Phillips" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > It fails because it is syntactically incorrect. You have brackets
in
> >> >> > the
> >> >> > wrong place.
> >> >> >
> >> >> > This works, or at least doesn't throw an error
> >> >> >
> >> >> > Dim sFormula As String
> >> >> >
> >> >> > sFormula = _
> >> >> >
> >> >
> >
"=IF(C2="""",""1"",IF(AND(A2=""enddate"",E2<>""01/01/01"",E2<NOW()+14),""2""
> >> >> >
> >> >
> >
,if(and(A2=""startem"",Date(0,Month(E2),Day(E2))>Date(0,Month(Now()),Day(Now
> >> >> > ()))),E2=""3"",""XXX"")))"
> >> >> > ActiveCell.Formula = sFormula
> >> >> >
> >> >> > The way I checked it was to take the string you were trying to
use,
> >> >> > display
> >> >> > that in the immediate window using the print method (? in the
> > immediate
> >> >> > window), and then take the displayed value and stick it in a cell.
> > Much
> >> >> > easier to see the problem then.
> >> >> >
> >> >> > --
> >> >> > HTH
> >> >> >
> >> >> > Bob Phillips
> >> >> >
> >> >> > (remove nothere from email address if mailing direct)
> >> >> >
> >> >> > "jocker" <[email protected]> wrote in message
> >> >> > news:[email protected]...
> >> >> >> Sorry, Bob. I did see your reply as to how to do it, but not on
why
> > my
> >> >> >> expanded code fails.Take pity on a pensioner trying to help his
> >> > daughter.
> >> >> >>
> >> >> >>
> >> >> >> "Bob Phillips" <[email protected]> wrote in
message
> >> >> >> news:%[email protected]...
> >> >> >> >I answered this in the other thread when you asked it yesterday.
> >> >> >> >
> >> >> >> > --
> >> >> >> > HTH
> >> >> >> >
> >> >> >> > Bob Phillips
> >> >> >> >
> >> >> >> > (remove nothere from email address if mailing direct)
> >> >> >> >
> >> >> >> > "jocker" <[email protected]> wrote in message
> >> >> >> > news:[email protected]...
> >> >> >> >> I have spent hours trying to correct this Excel code and am
> >> >> >> >> considering
> >> >> >> >> resigning.
> >> >> >> >> Can anyone help ?
> >> >> >> >>
> >> >> >> >> ActiveCell.Formula =
> >> >> >> >>
> >> >> >> >
> >> >> >
> >> >
> >
"=IF((C2)="""",""1"",IF(AND((A2)=""enddate"",(E2)<>""01/01/01"",(E2)<NOW()+1
> >> >> >> >
> >> >> >
> >> >
> >
4),""2"",if(and((A2)=""startem"",date(0,month(e2),day(e2))>date(0,month(now(
> >> >> >> > )),day(now())),(e2)=""3""),""XXX""))"
> >> >> >> >>
> >> >> >> >>
> >> >> >> >
> >> >> >> >
> >> >> >>
> >> >> >>
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

It works a treat, many thanks
jeff
++++++++++++++++++++++
"Bob Phillips" <[email protected]> wrote in message
news:elx%[email protected]...
> Ron,
>
> I think the problem is arising from copying from the NG getting linefeeds
> embedded. This
>
> ?"=IF((C2)="""",""1"",IF(AND((A2)=""enddate"",(E2)<>""01/01/01"",(E2)<NOW()+
> 14),""2"",if(and((A2)=""startem"",date(0,month(e2),day(e2))>date(0,month(now
> ()),day(now())),(e2)=""3""),""XXX""))"
>
> has a CR after the + and the now on the second line. Ensure that you
> remove
> them so that the string is contiguous, it should work okay then.
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Ron Dean" <[email protected]> wrote in message
> news:[email protected]...
>> Yes, I entered it without the "Activecell" phrase. Even when I copy &
> paste
>> the code you put in the Immediate window, I get "Compile Error. Expected:
>> Line number or label or statement or end of statement
>>
>> Sorry to bother you again
>> +++++++++++++
>>
>>
>> "Bob Phillips" <[email protected]> wrote in message
>> news:[email protected]...
>> > You shouldn't get a compile error as you are just printing a string.
>> > You
>> > did
>> > only copy the parts between the first and last quotes, not the
> Activecell
>> > bit as well?
>> >
>> > --
>> > HTH
>> >
>> > Bob Phillips
>> >
>> > (remove nothere from email address if mailing direct)
>> >
>> > "jocker" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> That's what I did but on hitting the Enter key all I get is "compile
>> > error"
>> >> and nothing in the Intermediate window changes
>> >>
>> >> ++++++++++++++++
>> >>
>> >>
>> >> "Bob Phillips" <[email protected]> wrote in message
>> >> news:%[email protected]...
>> >> > Okay, I'll try.
>> >> >
>> >> > Your original formula was
>> >> >
>> >> > ActiveCell.Formula =
>> >> >
>> >
> "=IF((C2)="""",""1"",IF(AND((A2)=""enddate"",(E2)<>""01/01/01"",(E2)<NOW()+1
>> >> >
>> >
> 4),""2"",if(and((A2)=""startem"",date(0,month(e2),day(e2))>date(0,month(now(
>> >> > )),day(now())),(e2)=""3""),""XXX""))"
>> >> >
>> >> > I took this part and in the immediate window in the VBIDE (Ctrl-G) I
>> >> > did
>> >> >
>> >> >
>> >
> ?"=IF((C2)="""",""1"",IF(AND((A2)=""enddate"",(E2)<>""01/01/01"",(E2)<NOW()+
>> >> >
>> >
> 14),""2"",if(and((A2)=""startem"",date(0,month(e2),day(e2))>date(0,month(now
>> >> > ()),day(now())),(e2)=""3""),""XXX""))"
>> >> >
>> >> > the ? is print. This gave a result of
>> >> >
>> >> >
>> >
> =IF((C2)="","1",IF(AND((A2)="enddate",(E2)<>"01/01/01",(E2)<NOW()+14),"2",if
>> >> >
>> >
> (and((A2)="startem",date(0,month(e2),day(e2))>date(0,month(now()),day(now())
>> >> > ),(e2)="3"),"XXX"))
>> >> >
>> >> >
>> >> > which I could copy, paste ina worksheet ecll, and see the problem
> more
>> >> > easily.
>> >> >
>> >> > --
>> >> > HTH
>> >> >
>> >> > Bob Phillips
>> >> >
>> >> > (remove nothere from email address if mailing direct)
>> >> >
>> >> > "jocker" <[email protected]> wrote in message
>> >> > news:[email protected]...
>> >> >> Many thanks, Bob. Can u explain in more detail how you found the
>> >> >> fault.
>> >> >>
>> >> >> Not sure about "print method"
>> >> >>
>> >> >> jeff
>> >> >> +++++++++++++++++++++++++++++++++++++
>> >> >> "Bob Phillips" <[email protected]> wrote in message
>> >> >> news:[email protected]...
>> >> >> > It fails because it is syntactically incorrect. You have brackets
> in
>> >> >> > the
>> >> >> > wrong place.
>> >> >> >
>> >> >> > This works, or at least doesn't throw an error
>> >> >> >
>> >> >> > Dim sFormula As String
>> >> >> >
>> >> >> > sFormula = _
>> >> >> >
>> >> >
>> >
> "=IF(C2="""",""1"",IF(AND(A2=""enddate"",E2<>""01/01/01"",E2<NOW()+14),""2""
>> >> >> >
>> >> >
>> >
> ,if(and(A2=""startem"",Date(0,Month(E2),Day(E2))>Date(0,Month(Now()),Day(Now
>> >> >> > ()))),E2=""3"",""XXX"")))"
>> >> >> > ActiveCell.Formula = sFormula
>> >> >> >
>> >> >> > The way I checked it was to take the string you were trying to
> use,
>> >> >> > display
>> >> >> > that in the immediate window using the print method (? in the
>> > immediate
>> >> >> > window), and then take the displayed value and stick it in a
>> >> >> > cell.
>> > Much
>> >> >> > easier to see the problem then.
>> >> >> >
>> >> >> > --
>> >> >> > HTH
>> >> >> >
>> >> >> > Bob Phillips
>> >> >> >
>> >> >> > (remove nothere from email address if mailing direct)
>> >> >> >
>> >> >> > "jocker" <[email protected]> wrote in message
>> >> >> > news:[email protected]...
>> >> >> >> Sorry, Bob. I did see your reply as to how to do it, but not on
> why
>> > my
>> >> >> >> expanded code fails.Take pity on a pensioner trying to help his
>> >> > daughter.
>> >> >> >>
>> >> >> >>
>> >> >> >> "Bob Phillips" <[email protected]> wrote in
> message
>> >> >> >> news:%[email protected]...
>> >> >> >> >I answered this in the other thread when you asked it
>> >> >> >> >yesterday.
>> >> >> >> >
>> >> >> >> > --
>> >> >> >> > HTH
>> >> >> >> >
>> >> >> >> > Bob Phillips
>> >> >> >> >
>> >> >> >> > (remove nothere from email address if mailing direct)
>> >> >> >> >
>> >> >> >> > "jocker" <[email protected]> wrote in message
>> >> >> >> > news:[email protected]...
>> >> >> >> >> I have spent hours trying to correct this Excel code and am
>> >> >> >> >> considering
>> >> >> >> >> resigning.
>> >> >> >> >> Can anyone help ?
>> >> >> >> >>
>> >> >> >> >> ActiveCell.Formula =
>> >> >> >> >>
>> >> >> >> >
>> >> >> >
>> >> >
>> >
> "=IF((C2)="""",""1"",IF(AND((A2)=""enddate"",(E2)<>""01/01/01"",(E2)<NOW()+1
>> >> >> >> >
>> >> >> >
>> >> >
>> >
> 4),""2"",if(and((A2)=""startem"",date(0,month(e2),day(e2))>date(0,month(now(
>> >> >> >> > )),day(now())),(e2)=""3""),""XXX""))"
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >
>> >> >> >> >
>> >> >> >>
>> >> >> >>
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>