Link to home
Avatar of Eduardo Aviles
Eduardo AvilesFlag for United States of America

asked on

Help Please! VbScript and Excel Formula

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",""))
ASKER CERTIFIED SOLUTION
Avatar of Bill Prew
Bill Prew

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Eduardo Aviles

ASKER

Thank you Bill Prew, it is exactly what I needed.  Thank you
Avatar of Bill Prew
Bill Prew

Welcome.


»bp