if & and code problem
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" wrote in message
. nl...
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" wrote in message
...
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" wrote in message
. nl...
Many thanks, Bob. Can u explain in more detail how you found the fault.
Not sure about "print method"
jeff
+++++++++++++++++++++++++++++++++++++
"Bob Phillips" wrote in message
...
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))D ate(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" wrote in message
. nl...
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" wrote in message
...
I answered this in the other thread when you asked it yesterday.
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
"jocker" wrote in message
. nl...
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""))"
|