| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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""))" |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
if & and code problem
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""))" |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
if & and code problem
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""))" |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
if & and code problem
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""))" |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
if & and code problem
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""))" |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
if & and code problem
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""))" |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
if & and code problem
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""))" |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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""))" |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
if & and code problem
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" wrote in message ... 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""))" |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
if & and code problem
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" wrote in message . nl... 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" wrote in message ... 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""))" |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
if & and code problem
It works a treat, many thanks
jeff ++++++++++++++++++++++ "Bob Phillips" wrote in message ... 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" wrote in message . nl... 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" wrote in message ... 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""))" |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| If/then code problem | Excel Programming | |||
| Can anyone see the problem with this code? | Excel Programming | |||
| code problem | Excel Programming | |||
| Problem with this code | Excel Programming | |||
| Code problem | Excel Programming | |||
Linear Mode
