VBA Code to Multiply a range of cells by a reference cell

Question

Hello,&nbsp;I am creating a form for internal use for our employees. I am trying to make it as user friendly as possible for them, as they have varying levels of excel knowledge.&nbsp;&nbsp;There is a range of columns (K8:K28) that need to be multiplied by a percentage. The percentage is different for each project, so the form is set up with a formula (=1-M8) to calculate this percentage based on the data. This reference cell is K7.&nbsp;&nbsp;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?&nbsp;Thank you for your help!

mathetes · Answer

stephxian&nbsp;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?&nbsp;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.&nbsp;But it's also not totally clear what you're asking. The formula&nbsp;=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?&nbsp;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.&nbsp;Where is the result of whatever multiplication takes place to appear?&nbsp;&nbsp;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?

stephxian · Answer

mathetes&nbsp;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.&nbsp;&nbsp;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?&nbsp;Thank you for your time.

mathetes · Answer

stephxian&nbsp;&nbsp;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.&nbsp;&nbsp;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.

stephxian · Answer

mathetes&nbsp;&nbsp;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?&nbsp;Thank you for your help.

mathetes · Answer

stephxian&nbsp;&nbsp;And I know of no way to enter something into a cell and have Excel display in the same cell a value that is different from the one entered. That would, in my opinion, be the Excel equivalent of lifting yourself up by your own bootstraps&nbsp;Please recall this sentence from your original posting:&nbsp;I am trying to make it as user friendly as possible for them, as they have varying levels of excel knowledge.&nbsp;&nbsp;Now, I don't know about you, but if I were a beginner in Excel, asked to enter a number--let's say it's 100--in a cell, and as soon as I enter it, what appears is 75, I'd think "What did I do wrong?" "I'm sure I entered 100; guess I need to&nbsp;enter it again." or&nbsp;something along those lines. &nbsp;Maybe your understanding of "user friendly" differs from mine. I think making it possible to enter the number 100 in a given cell that corresponds to whatever it is that I'm responsible for, to be limited in my abilities to enter at random places on that sheet......and then to see the calculation done (and be told what it is, what it represents) on the column next door......THAT would be user friendly. I see my entry AND I see the calculated result. If you really want to be friendly, you can even explain the specific discount that was applied so they, although naive re Excel, may be able to verify it on their handheld calculator.&nbsp;So I'm suggesting a different way, consisting ofentry into a cellcalculation in another columnthat other column could be on the same page, immediately adjacent, or on another pageI was demonstrating a method. If you disagree with the thinking, then please explainhow it's not lifting yourself up by your own bootstrapsand how it's user friendly (assuming it might be possible for a VBA routine to accomplish said lifting by the bootstraps)&nbsp;

Forum Discussion

VBA Code to Multiply a range of cells by a reference cell

Share

Resources