Adding Days to a Date in VBA

[SailorJerry7030]

Here's the current code:

'SH DATA COL P = DUE DATE
Worksheets("SH Data").Range("P2").Formula = "=IF(AND(OR(J2=""NEWCLAIM2"",J2=""WEBRECVD"",J2=""KECREATED""),ISBLANK(M2)=TRUE)=TRUE,WORKDAY(D2,O2),IF(Q2=""REWORK"",WORKDAY(D2,O2)," _
& "IF(OR(Q2=""QUESTION"",Q2=""RQMT RECVD"",Q2=""ACTIONABLE PENDING""),WORKDAY(C2,O2)," _
& "IF(AND(OR(Q2=""NEW CLAIM"",Q2=""ACTIONABLE PENDING""),OR(J2=""AALSAPPROV"",J2=""DISBDONE"",J2=""OK"",J2=""PROCESSED"",J2=""CDSTIMEOUT"",J2=""AWDNOAPPRV"",J2=""NOAALSAPRV"")),WORKDAY(C2,O2)," _
& "IF(AND(ISBLANK(M2)=TRUE)=TRUE,WORKDAY(C2,O2),WORKDAY(M2,O2))))))"
'New code
Worksheets("SH Data").Range("P2").Formula = "=IF(AND(G2=""DIPAYMENTS"",J2=""PEND30""),D2+O2,"")"


Essentially it runs through the first formula, then goes back through and if the AND statement is met for G2 & J2 it adds Column D2 (Date) and O2 (in this situation "30") for 30 days. If it's not met, I want it to keep the results of the first long formula. Unfortunately, when I run it as-is, I get "Run-Time error '1004': Application-defined or object defined error. If I remove the false statement it works fine once the AND criteria is met, but everything from the first formula is overwritten with "FALSE". Any help please?

 

[Legacy 421546]

You start off in VBA and create formulas in the worksheet. Why?
Start from scratch and solve everything in VBA with loops and a lot of comments so the code is maintainable. Now it is not.

 

[SailorJerry7030]

You start off in VBA and create formulas in the worksheet. Why?
Start from scratch and solve everything in VBA with loops and a lot of comments so the code is maintainable. Now it is not.

Ok, first I'm not well versed in VBA code. I didn't write this (other than the new code) and just maintain it when changes are needed. Second, "Start from scratch" is not helpful. Could you maybe explain why the new line of code isn't working properly?

 

[JoeMo]

Double-up the quote marks on the new line:

"=IF(AND(G2=""DIPAYMENTS"",J2=""PEND30""),D2+O2,"""")"

 

[Legacy 421546]

Your code is a mess! Tidy it up, i.e. start from scratch. It IS helpful!

 

[SailorJerry7030]

Double-up the quote marks on the new line:

"=IF(AND(G2=""DIPAYMENTS"",J2=""PEND30""),D2+O2,"""")"

Ok, I'll admit that was a silly mistake. However, it overwrites the first formula's results with blanks. Is there a way to code it so that the first formula's results remain there? If G2=Dipayments and J2= PEND30, add D2+O2, otherwise don't change what's currently inside the cell.

 

[SailorJerry7030]

Your code is a mess! Tidy it up, i.e. start from scratch. It IS helpful!

Haha, again...not my code. I couldn't write this or something better if I tried. I'm just trying to implement changes into the code since the original writer is gone.

 

[JoeMo]

Ok, I'll admit that was a silly mistake. However, it overwrites the first formula's results with blanks. Is there a way to code it so that the first formula's results remain there? If G2=Dipayments and J2= PEND30, add D2+O2, otherwise don't change what's currently inside the cell.

Not if you write anew formula to the cell. Since you're using VBA anyway, you could do it with a few lines of code like this:

With Worksheets("SH Data").Range("P2")
      If [G2] = "Dipayments" and [J2] = "PEND30" Then .Value = [D2] + [O2]
End With

 

[SailorJerry7030]

Not if you write anew formula to the cell. Since you're using VBA anyway, you could do it with a few lines of code like this:

With Worksheets("SH Data").Range("P2")
      If [G2] = "Dipayments" and [J2] = "PEND30" Then .Value = [D2] + [O2]
End With

It seems so simple, I dropped that code in just below the first long formula and it seems the sheet is still showing the Due Date column as adding off column C2. I should add that the condition underlined is met when G2=DIPAYMENTS and J2=PEND30, Q2 would equal ACTIONABLE PENDING. But with your formula at the end, it should overwrite it shouldn't it? I don't show anywhere else in the code where range P2 is referenced:

Worksheets("SH Data").Range("P2").Formula = "=IF(AND(OR(J2=""NEWCLAIM2"",J2=""WEBRECVD"",J2=""KECREATED""),ISBLANK(M2)=TRUE)=TRUE,WORKDAY(D2,O2),IF(Q2=""REWORK"",WORKDAY(D2,O2)," _
& "IF(OR(Q2=""QUESTION"",Q2=""RQMT RECVD"",Q2=""ACTIONABLE PENDING""),WORKDAY(C2,O2)," _
& "IF(AND(OR(Q2=""NEW CLAIM"",Q2=""ACTIONABLE PENDING""),OR(J2=""AALSAPPROV"",J2=""DISBDONE"",J2=""OK"",J2=""PROCESSED"",J2=""CDSTIMEOUT"",J2=""AWDNOAPPRV"",J2=""NOAALSAPRV"")),WORKDAY(C2,O2)," _
& "IF(AND(ISBLANK(M2)=TRUE)=TRUE,WORKDAY(C2,O2),WORKDAY(M2,O2))))))"
'New code
With Worksheets("SH Data").Range("P2")
If [G2] = "DIPAYMENTS" And [J2] = "PEND30" Then .Value = [D2] + [O2]
End With

 

[JoeMo]

I have no idea what you want your code to accomplish or what your spreadsheet looks like so I can't provide much help.