VBA Cell Value Update

  • Short story: I need to update a budget template to automatically multiply a cell value by that of another. There are two ranges involved, E62:P75 and E84:P86. Right now I'm just trying to get it working with the first range. In the corresponding D for a cell is the per month rate. For each month E:P, you should be enter in an integer and the script then replaces that value with the value times per month rate. If the field is blank or not an integer, then it defaults to 0.

    I keep getting "Run-time error: '-2147417848 (80010108)': Method 'Value' of object 'Range' failed".

    Here's my code:

    I've also attached the spreadsheet. Any suggestions?

  • Re: VBA Cell Value Update

    Did you really want to iterate the whole intersected range? The way you have it now, one entry in the range will loop through several cells. If you posted one sample input and the expected result, I could better see just what you need and offer a more simple solution I suspect.

    In any case, when working with event code like that, one usually wants to add Application code for speed alone if nothing else.

  • Re: VBA Cell Value Update

    Wow, that does exactly what I was looking for.

    I know the code is a bit wasteful in that it forces an update to all of the cells in the range at the same time, but that also ensures that all entries are validated regardless of what is modified.

    As for an example of what I would ideally want to see happen, if you enter 3 into E62, it updates to 3*D62, or 17,490. Having it update all the fields at once is cool, but I do understand it takes more effort for the system to do so. So, is it possible to limit it to updating only the modified field if the field was in the specified range, or would that be getting too complex of code that it wouldn't be worth doing?

  • Re: VBA Cell Value Update

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!