Forum Discussion
VBA Code to Multiply a range of cells by a reference cell
What I am hoping for, is possibly some VBA code that will automatically multiply cell K7 when a value is entered into one of the cells in the range (K8:K28). Is this possible?
Most things like this are possible. What's not clear is why you're specifying VBA code at this point. VBA and macros are sometimes needed, to be sure. What you've described doesn't yet sound like one of the situations that require one of them. And for novice users in particular, VBA/macro routines can make things decidedly unfriendly. So may we put that assumption aside for the moment.
But it's also not totally clear what you're asking. The formula =1-M8, for instance, makes no reference to cell K7. Or is that value, the value of 1-M8, what is in cell K7? How, in other words, do those two components fit together in the scenario of an entry being made in the range K8:K28?
So let's assume I make an entry in cell K20 ... Please describe in words what is supposed to happen involving the ingredients named above.
Where is the result of whatever multiplication takes place to appear?
Is it possible (without violating any proprietary or confidential info) for you to post a copy of the spreadsheet as it currently exists, in OneDrive or GoogleDrive, pasting a link here granting edit access to it?
Thank you for your reply. Yes, the value in K7 is the formula =1-M8. If you were to enter a value into K20, I want that cell to automatically display the value of K20*K7.
I am certainly open to other ways to accomplish this other than VBA. I went down that road because other options seemed to required that the data already be filled in or required that K7 is a real number and not a formula. I am the only one setting up the form using the code. The other users will only be inputting the data. They will not be messing with any coding or formulas. I am hoping to get the range to calculate for them automatically when they enter the data because some may not do well having to enter the formula themselves for each data entry. Does that make sense?
Thank you for your time.
If you enter a value into cell K20, it would override any formula in K20. So that calculation needs to be in some other cell. There are multiple ways to do that.
I've attached a spreadsheet with an example of one way. There are two sheets, one for Entry, the other for Output. Comments are on the sheet explaining in a bit more detail. Feel free to come back with any questions.
Yes, this is the trouble I am having and why I was looking for code instead of a formula. My users need to be able to do nothing more than to enter a value in cell range (K8:K28) and what is displayed in the cell is the result of that value multiplied by K7. Does that help clarify what I am trying to do?
Thank you for your help.
