Help Please! VbScript and Excel Formula

Eduardo Aviles
Eduardo Aviles used Ask the Experts™
on
I have a VbScript that creates an Excel Spreadsheet and writes in formula. I have a formula that I am trying to add a function with it but it does not give me the result I am looking for. The below formula should add the current date whenever approved w/ comments or approved is selected, but at the time of creation it hard codes the date for the creation date instead.  Experts please tell me what I am doing wrong.

Formula:  

objSheet.Cells(5, 2).formula = "=IF(G7=" & Chr(34) & "Approved w/Comments" & Chr(34) & ","  & chr(34) & FormatDateTime(Now(),vbShortDate) & Chr(34) & ",IF(G7=" &chr(34)& "Approved" &chr(34)& "," &chr(34)& FormatDateTime(Now(),vbShortDate) &chr(34)& "," & chr(34) & chr(34) & "))"

Output:
=IF(G7="Approved w/Comments","8/21/2018",IF(G7="Approved","8/21/2018",""))
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
IT / Software Engineering Consultant
Top Expert 2016
Commented:
This should do what you described, picking up the current date.

objSheet.Cells(5, 2).formula = "=IF(G7=" & Chr(34) & "Approved w/Comments" & Chr(34) & ",TODAY(),IF(G7=" & chr(34) & "Approved" & chr(34)& ",TODAY()," & chr(34) & chr(34) & "))"

Open in new window


»bp

Author

Commented:
Thank you Bill Prew, it is exactly what I needed.  Thank you
Bill PrewIT / Software Engineering Consultant
Top Expert 2016

Commented:
Welcome.


»bp

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial