Vba code when cell in range returns a certain value, enter formula in nearby cell

I know how to do this for one cell, but it needs to be with a range.

So if C12 says PayPal Fees, then E12 needs to return a formula. If C13 says PayPal Fees, then E13 needs to return a formula, etc.

If C12 says PayPal Fees, then the formula in E12 is =(E11*'-'Setup Page'!$Q$44)-'Setup Page'!$S$44
If C13 says PayPal Fees, then the formula in E13 is = (E12*'-'Setup Page'!$Q$44)-'Setup Page'!$S$44

I am running into problems for two reasons, the first reference in the formula always changes. If the formula is in row 12, the reference is E and the row above so 11. I don't know how to get it to change relative to the row the formula is in.

The other reason is I don't want to have to say if C12 changes then this; if C13 changes then this; etc. one at a time because it is a long range. C12:C190. So anytime just ONE cell in that range changes, then the E column in that row needs the formula.

Below was what I knew how to do. I know the code is wrong, but it worked in the sense that when I changed C12 to PayPal Fees, it did put the formula in E12. But in my code, the first formula reference is stagnant and as mentioned it needs to change. Then it, of course, tried to check all the other cells in the range, which were currently blank so it killed the page.

Please Login or Register  to view this content.

Thanks. I am new to VBA. Cells(c1.Row, "E") makes a lot of sense. What does If Not Intersect mean?

Thanks. Your code worked. I just had to change the last references since they needed to be absolute: Setup Page'!R44C17)-'Setup Page'!R44C19"

Originally Posted by dsrt16

What does If Not Intersect mean?

Intersect is a function that returns a Range object that is the intersection of all the ranges listed as arguments to it. In this case it will give you the intersection between cell c1 and Range rngInvoiceCategory. If c1 is in rngInvoiceCategory, then their intersection is c1. If c1 is not in rngInvoiceCategory, then the result is Nothing. Nothing is a special value for an object that indicates that the object is not set to reference anything (this would require a longer explanation about how objects work). For our purposes here, if the function returns Nothing, it means there is no intersection among the ranges listed. Putting a Not in front means the opposite. So in this particular case,

Not Intersect(c1, rngInvoiceCategory) Is Nothing

means "c1 is within range rngInvoiceCategory"