Enter a formula in VB

Matt · May 16th, 2002, 05:58 AM

I have been using this formula entered directly into a cell and it works fine, when I try to enter it through VB, it brings up a syntax error stopping at the 4th quote mark. Any ideas on how I can avoid this.

Worksheets("sheet1").Range("e42").formula = "=if Q42 <> "", CONCATENATE("Job Completed "&TEXT(Q42,"dd/mm/yy")"
),IF(P42<>"",CONCATENATE("Work Started "&TEXT(P42,"dd/mm/yy")),IF(O42<>"",CONCATENATE("Scheduled To Start "&TEXT(O42,"dd/mm/yy")),IF(H42<>"","Programmer Allocated",IF(M42<>"",CONCATENATE("Received By IT "&TEXT(M42,"dd/mm/yy")),""))))))"

thanks

Matt

dimrod · May 16th, 2002, 06:14 AM

I don't think that the formula you posted can work in a cell.

Anyway, try this (might not work - haven't tried it) :-

=IF(Q42 <> "", CONCATENATE("Job Completed "&TEXT(Q42,"dd/mm/yy")),IF(P42<>"",CONCATENATE("Work Started "&TEXT(P42,"dd/mm/yy")),IF(O42<>"",CONCATENATE("Scheduled To Start "&TEXT(O42,"dd/mm/yy")),IF(H42<>"","Programmer Allocated",IF(M42<>"",CONCATENATE("Received By IT "&TEXT(M42,"dd/mm/yy")),"")))))

Ivan F Moala · May 16th, 2002, 06:24 AM

Quote:

On 2002-05-16 04:58, Matt wrote:
I have been using this formula entered directly into a cell and it works fine, when I try to enter it through VB, it brings up a syntax error stopping at the 4th quote mark. Any ideas on how I can avoid this.

Worksheets("sheet1").Range("e42").formula = "=if Q42 <> "", CONCATENATE("Job Completed "&TEXT(Q42,"dd/mm/yy")"
),IF(P42<>"",CONCATENATE("Work Started "&TEXT(P42,"dd/mm/yy")),IF(O42<>"",CONCATENATE("Scheduled To Start "&TEXT(O42,"dd/mm/yy")),IF(H42<>"","Programmer Allocated",IF(M42<>"",CONCATENATE("Received By IT "&TEXT(M42,"dd/mm/yy")),""))))))"

thanks

Matt

Hi Matt
I believe your formula should be;
'
'



Worksheets("sheet1").Range("e42").Formula _

"=IF(Q42<>"""",CONCATENATE(""Job Completed ""&TEXT(Q42,""dd/mm/yy""))," _

& "IF(P42<>"""",CONCATENATE(""Work Started ""&TEXT(P42,""dd/mm/yy""))," _

& "IF(O42<>"""",CONCATENATE(""Scheduled To Start ""&TEXT(O42,""dd/mm/yy""))," _

& "IF(H42<>"""",""Programmer Allocated"",IF(M42<>"""",CONCATENATE(""Received By IT ""&TEXT(M42,""dd/mm/yy"")),"""")))))"

Matt · May 16th, 2002, 07:53 AM

Thanks guys

Ivan - tried your method and getting the error object doesn't support this property/method.

Any ideas

thanks

Matt

[ This Message was edited by: Matt on 2002-05-16 06:56 ]

dimrod · May 16th, 2002, 07:58 AM

I think Ivan inadvertently missed out an = sign. First line should be :-

Worksheets("sheet1").Range("e42").Formula = _

Ivan F Moala · May 16th, 2002, 08:03 AM

Quote:

On 2002-05-16 06:53, Matt wrote:
Thanks guys

Ivan - tried your method and getting the error object doesn't support this property/method.

Any ideas

thanks

Matt

[ This Message was edited by: Matt on 2002-05-16 06:56 ]

Matt..sorry silly mistake try:
'
'



Worksheets("Sheet1").Range("e42").Formula = _

"=IF(Q42<>"""",CONCATENATE(""Job Completed ""&TEXT(Q42,""dd/mm/yy""))," _

& "IF(P42<>"""",CONCATENATE(""Work Started ""&TEXT(P42,""dd/mm/yy""))," _

& "IF(O42<>"""",CONCATENATE(""Scheduled To Start ""&TEXT(O42,""dd/mm/yy""))," _

& "IF(H42<>"""",""Programmer Allocated"",IF(M42<>"""",CONCATENATE(""Received By IT ""&TEXT(M42,""dd/mm/yy"")),"""")))))"

Ivan F Moala · May 16th, 2002, 08:05 AM

Quote:

On 2002-05-16 06:58, dimrod wrote:
I think Ivan inadvertently missed out an = sign. First line should be :-

Worksheets("sheet1").Range("e42").Formula = _

Thanks Dimrod

maybe a liitle less haste........

Matt · May 16th, 2002, 08:07 AM

Top Man, that's perfect, thanks again to both of you!

Matt · May 16th, 2002, 08:23 AM

Sorry Guys to bother you again, I'm trying to force a variable into the equation "row", which changes the row that the formula calculates. At the moment, the variable appears in the formula as text and not as the variable integer as it should. I know the variable is Ok as it appears in the same procedure out of a formula and functions correctly. Hope you can help

Formula used:

Worksheets("sheet1").Range("e" & row).Formula = "=IF(Q & row<>"""",CONCATENATE(""Job Completed ""&TEXT(Q & row,""dd/mm/yy""))," _
& "IF(P & row<>"""",CONCATENATE(""Work Started ""&TEXT(P & row,""dd/mm/yy""))," _
& "IF(O & row<>"""",CONCATENATE(""Scheduled To Start ""&TEXT(O & row,""dd/mm/yy""))," _
& "IF(H & row<>"""",""Programmer Allocated"",IF(M & row<>"""",CONCATENATE(""Received By IT ""&TEXT(M & row,""dd/mm/yy"")),"""")))))"

Formula that appears in cell

=IF(Q & row<>"",CONCATENATE("Job Completed "&TEXT(Q & row,"dd/mm/yy")),IF(P & row<>"",CONCATENATE("Work Started "&TEXT(P & row,"dd/mm/yy")),IF(O & row<>"",CONCATENATE("Scheduled To Start "&TEXT(O & row,"dd/mm/yy")),IF(H & row<>"","Programmer Allocated",IF(M & row<>"",CONCATENATE("Received By IT "&TEXT(M & row,"dd/mm/yy")),"")))))
thanks

Matt

[ This Message was edited by: Matt on 2002-05-16 07:24 ]

[ This Message was edited by: Matt on 2002-05-16 07:33 ]

kkknie · May 16th, 2002, 08:45 AM

Try this:

Worksheets("sheet1").Range("e" & Row).Formula = "=IF(Q" & Row & "<>"""",CONCATENATE(""Job Completed ""&TEXT(Q" & Row & ",""dd/mm/yy""))," _
& "IF(P" & Row & "<>"""",CONCATENATE(""Work Started ""&TEXT(P" & Row & ",""dd/mm/yy""))," _
& "IF(O" & Row & "<>"""",CONCATENATE(""Scheduled To Start ""&TEXT(O" & Row & ",""dd/mm/yy""))," _
& "IF(H" & Row & "<>"""",""Programmer Allocated"",IF(M" & Row & "<>"""",CONCATENATE(""Received By IT ""&TEXT(M" & Row & ",""dd/mm/yy"")),"""")))))"

Since you are just creating a huge string, you have to keep the row variable outside of the quotes so that it will use its value and not the string row.

K

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 16th, 2002, 05:58 AM	#1
Matt Board Regular Join Date: Feb 2002 Location: England Posts: 212	I have been using this formula entered directly into a cell and it works fine, when I try to enter it through VB, it brings up a syntax error stopping at the 4th quote mark. Any ideas on how I can avoid this. Worksheets("sheet1").Range("e42").formula = "=if Q42 <> "", CONCATENATE("Job Completed "&TEXT(Q42,"dd/mm/yy")" ),IF(P42<>"",CONCATENATE("Work Started "&TEXT(P42,"dd/mm/yy")),IF(O42<>"",CONCATENATE("Scheduled To Start "&TEXT(O42,"dd/mm/yy")),IF(H42<>"","Programmer Allocated",IF(M42<>"",CONCATENATE("Received By IT "&TEXT(M42,"dd/mm/yy")),""))))))" thanks Matt

May 16th, 2002, 06:14 AM	#2
dimrod Join Date: May 2002 Posts: 73	I don't think that the formula you posted can work in a cell. Anyway, try this (might not work - haven't tried it) :- =IF(Q42 <> "", CONCATENATE("Job Completed "&TEXT(Q42,"dd/mm/yy")),IF(P42<>"",CONCATENATE("Work Started "&TEXT(P42,"dd/mm/yy")),IF(O42<>"",CONCATENATE("Scheduled To Start "&TEXT(O42,"dd/mm/yy")),IF(H42<>"","Programmer Allocated",IF(M42<>"",CONCATENATE("Received By IT "&TEXT(M42,"dd/mm/yy")),"")))))

May 16th, 2002, 07:53 AM	#4
Matt Board Regular Join Date: Feb 2002 Location: England Posts: 212	Thanks guys Ivan - tried your method and getting the error object doesn't support this property/method. Any ideas thanks Matt [ This Message was edited by: Matt on 2002-05-16 06:56 ]

May 16th, 2002, 07:58 AM	#5
dimrod Join Date: May 2002 Posts: 73	I think Ivan inadvertently missed out an = sign. First line should be :- Worksheets("sheet1").Range("e42").Formula = _

May 16th, 2002, 08:07 AM	#8
Matt Board Regular Join Date: Feb 2002 Location: England Posts: 212	Top Man, that's perfect, thanks again to both of you!

May 16th, 2002, 08:23 AM	#9
Matt Board Regular Join Date: Feb 2002 Location: England Posts: 212	Sorry Guys to bother you again, I'm trying to force a variable into the equation "row", which changes the row that the formula calculates. At the moment, the variable appears in the formula as text and not as the variable integer as it should. I know the variable is Ok as it appears in the same procedure out of a formula and functions correctly. Hope you can help Formula used: Worksheets("sheet1").Range("e" & row).Formula = "=IF(Q & row<>"""",CONCATENATE(""Job Completed ""&TEXT(Q & row,""dd/mm/yy""))," _ & "IF(P & row<>"""",CONCATENATE(""Work Started ""&TEXT(P & row,""dd/mm/yy""))," _ & "IF(O & row<>"""",CONCATENATE(""Scheduled To Start ""&TEXT(O & row,""dd/mm/yy""))," _ & "IF(H & row<>"""",""Programmer Allocated"",IF(M & row<>"""",CONCATENATE(""Received By IT ""&TEXT(M & row,""dd/mm/yy"")),"""")))))" Formula that appears in cell =IF(Q & row<>"",CONCATENATE("Job Completed "&TEXT(Q & row,"dd/mm/yy")),IF(P & row<>"",CONCATENATE("Work Started "&TEXT(P & row,"dd/mm/yy")),IF(O & row<>"",CONCATENATE("Scheduled To Start "&TEXT(O & row,"dd/mm/yy")),IF(H & row<>"","Programmer Allocated",IF(M & row<>"",CONCATENATE("Received By IT "&TEXT(M & row,"dd/mm/yy")),""))))) thanks Matt [ This Message was edited by: Matt on 2002-05-16 07:24 ] [ This Message was edited by: Matt on 2002-05-16 07:33 ]

May 16th, 2002, 08:45 AM	#10
kkknie Board Regular Join Date: Apr 2002 Location: Greenwood, SC Posts: 677	Try this: Worksheets("sheet1").Range("e" & Row).Formula = "=IF(Q" & Row & "<>"""",CONCATENATE(""Job Completed ""&TEXT(Q" & Row & ",""dd/mm/yy""))," _ & "IF(P" & Row & "<>"""",CONCATENATE(""Work Started ""&TEXT(P" & Row & ",""dd/mm/yy""))," _ & "IF(O" & Row & "<>"""",CONCATENATE(""Scheduled To Start ""&TEXT(O" & Row & ",""dd/mm/yy""))," _ & "IF(H" & Row & "<>"""",""Programmer Allocated"",IF(M" & Row & "<>"""",CONCATENATE(""Received By IT ""&TEXT(M" & Row & ",""dd/mm/yy"")),"""")))))" Since you are just creating a huge string, you have to keep the row variable outside of the quotes so that it will use its value and not the string row. K