Not pasting second formula for blanks

Everything seems to check out, but can't get this to work.

Please Login or Register  to view this content.

My original formula does paste , but after that, I can't get the the blank cells to paste in second formula. I only added the copy/paste special in the code because I thought it would help to clear out the all the cells' first formula, but it didn't do anything.

Trying to get a calculated date from another table. But, if it doesn't exist in that table, then I just want it to calculate 15 days less than the Hearing Date column in the same table. Any ideas what I'm doing wrong?

Thank you!

first formula is :

Please Login or Register  to view this content.

Second formula is further down in the code, for the blank cells:

Please Login or Register  to view this content.

I came back to say this isn't actually solved. I'm still working on it. I think I do have a solution, but need to tweak it.

Originally Posted by jmklei0

... I can't get the the blank cells to paste in second formula ...

1. "Blank" cells are not "blank", there are formulas in them (?)
2. Do not make such long entries in your vbe code (text of formula) as it blurs the "overall picture" of what you want to do
Make the formula out of the smaller parts/blocks - see an example below
3. Attach an example with the appropriate data layout in the worksheet (false data)

Ad 2., e.g.:

Please Login or Register  to view this content.

@TMS, I appreciate all your help. I didn't feel like the other post was the same, as I had tangented off this original, but I'll go along with it. I've been trying to figure out the solution and things have changed along the way. There's no way I could post this whole code and the related workbooks. The entire macro literally pulls three workbooks together to make a worksheet with multiple conditional formats, then 8 pivot tables. I'm trying to snippet because all else would be a waste of time and I'm being generous not getting everybody to dig through all of the code.

This particular piece of code was originally bringing in a date from another table with a formula (if/match, which you can see above) That formula does its job. But, it came to my attention that not all of the dates were available from the lookup table. So, I'm trying to compensate by simply calculating the date (minus 15 days) instead, when they're missing. Ironically enough, if I can get this process correct, the final formula will be much longer - but one scientific step at time (and I don't need to muddy the question any more).

I do know that the PET Ev Due column cells and the Hearing Date cells are not technically blank (for a reason I do not know - they have 0 length and are ""), so special blank cells will not work in this case.

So far I've come up with this. I feel like it's really close to being correct, but I think the syntax for the integers (i) are misplaced. This code is the next line after the PET Evid Due column has its matches from the if/match formula. Then it runs this piece. This newest iteration doesn't seem to run (maybe Error goes to next line and skips this). You can see in the attachment what a sample would look like once we get to this point in the code. All I want it do is add the 15-less date to $F$6 in this sample, but leave the other rows alone.

Please Login or Register  to view this content.

As to just applying the HearingDate-15 formula once to the structured column (yes, everything in my code is based on structured tables), that won't work because some of the rows have the date set already from the if/match lookup formula that is already there.

Nonetheless, all of this is to say that I appreciate your help and if you have a suggestion for moving the integer syntax around, or anything thing else that would make it even simpler, thank you.

Originally Posted by porucha vevrku

2. Do not make such long entries in your vbe code (text of formula) as it blurs the "overall picture" of what you want to do
Make the formula out of the smaller parts/blocks - see an example below

Thank you. That is great advice. I do like keeping things easily readable and simple. My problem is that I have to get to that point. I'm working on so many formulas that keeping them straight is difficult enough. Hopefully I'll get to the point that I can update the syntax of my code like this. In fact, there are several parts that I could re-use pieces in multiple places.