.formula property referencing a defined string constant

I am running into a problem when I try to call out a string that I previously defined in the .formula property. Below is just part of the code. I define Replen to be one of 12 numbers.
The line in the code: .Formula = "=IF(And(A2=Replen), B2<(today()+7)), C2+C3, C3)" is what is giving me problems. I am able to get the code to work if I use an an OR statement embedded in the code listing all 12 items. The reason I dont' want to do that is because this list needs to be updated from time to time and I would have to update it in multiple places in the rest of the code.

Please Login or Register  to view this content.

DGagnon,

Thanks, I was looking at that took long yesterday. That solves why it didn't compile. It now compiles and runs, however, but there is still a a problem:

When it copies
=IF(And((A2=Replen), B2<(today()+7)), C2+C3, C3)
into the cells in the workbook, and I look at it by stepping through the code, the results do not properly add up. Instead, it gives #Name? because I do not think the work books recognizes what Replen is that I defined in the code.

Jerry,

Thanks a lot, that works as intended now.

There is just one little issue I didn't forsee because I was treating them as strings. Often these replen items undergo revisions. For example, 981541 could become 981541R1 and then two weeks later become 981541R2. When I was literally putting all replen item numbers (treating them as strings) in the .formula statement (the only way I originally got it to work), by putting in the substring 981541, it would work on 981541R1 and 981541R2, etc. Is there any way to add this ability to the function without being two complicated. Otherwise I'd have to be updating the function when something had a revision. The other solution would be to update all my .formula statements when there was a new replen item all together. Either way, you've been a huge help and clearly I need to expand my use of functions and not just sub routines. Thanks again.

Jerry and DGagnon,

Thanks for your help and insight. The code does exactly what I need it to do.