having issues with target cell containing a formula (office 2000)

[hawkingsright]

good afternoon. i am having some issues with the following line in VBA :

Please Login or Register  to view this content.

my problem is that the value does not update in A5. i believe this is because there is a formula in E120. is this the case, and if so, is there a work around? this is part of an inventory tracking sheet, and the formlula in E120 only shows a result if it pertains to particular criteria, i.e.: used, received, damaged, cut wrong, etc.:. this method is working fine for other cells that have drop in values from the user form, but it doesn't seem to work at all for cells that update with a formula.


thank you for your kind assistance.

[rvasquez]

Is this place in the on worksheet change change event?

[hawkingsright]

yes, it is in the change event. here is part of what i have:

Please Login or Register  to view this content.

this is the formula inside of E120:

Please Login or Register  to view this content.

what i want is to take the value of E120, (1, 2, etc) and add it to the value in A5. I am using this method in other cells that do not have formulas in them, and they update as expected. However, the example here will not. I'm sure I'm missing something very simple.

[rvasquez]

Try this,

It reference the cell in the formula in E120, because technically the target cell will be T104 but E120 will change as well.

Please Login or Register  to view this content.

[hawkingsright]

i'm afraid that didn't work. what i have done, is remove the formula from the sheet and i have it being entered with vba like this

Please Login or Register  to view this content.

now, if i hit delete on E120, A5 updates as it should. i have not been able to make this automated, however. i have tried

Please Login or Register  to view this content.

, but that doesn't work to update A5. is there a way to force a refresh after the formula is placed? i've even tried coding in a delete of the cell.

[JosephP]

I would use the change event (assuming those cells are manually entered) and monitor both reference cells

Please Login or Register  to view this content.

[hawkingsright]

thank you joseph. this is what i have at the moment:

Please Login or Register  to view this content.

What this is doing right now is counting up to 201 when a value of 1 is entered. A5 is a blank cell to start with. What I am trying to accomplish is when E120 returns a value of 1, or 2, or whatever is entered, it adds that number to the current value of A5. So, if A5 has a starting value of 5, and the result of the formula in E120 is 1, then A5 changes to 6.

[JosephP]

that's not what I posted. your formula in E120 depends on two cells - R104 and T104 - so the change event has to monitor both of those cells. how did the code I posted fail to do what you want?

[hawkingsright]

sorry...i had a typo. it works perfectly. thank you very much for taking the time to give me a hand!

[JosephP]

any time. :-)