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

Copper Contributor

Hello,

 

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. 

 

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. 

 

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?

 

Thank you for your help!

7 Replies

@stephxian 

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?

@mathetes 

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.

@stephxian 

 

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.

@mathetes 

 

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.

@stephxian 

 

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

mathetes_0-1673548864140.png

 

Please recall this sentence from your original posting: I am trying to make it as user friendly as possible for them, as they have varying levels of excel knowledge. 

 

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 enter it again." or something along those lines.

 

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.

 

So I'm suggesting a different way, consisting of

  • entry into a cell
    • calculation in another column
    • that other column could be on the same page, immediately adjacent, or on another page

I was demonstrating a method. If you disagree with the thinking, then please explain

  • how it's not lifting yourself up by your own bootstraps
  • and how it's user friendly (assuming it might be possible for a VBA routine to accomplish said lifting by the bootstraps)

 

@stephxian 

 

Apparently there is a macro that can be used to do this. I have seen other forums with this request and they were successful. The only thing is that though the function is the same, the data differs from mine. I do not know enough about macros to be able to modify the lines to accommodate my sheet. If I figure this out, I will share with you so we can learn together.

 

Thanks for your time.

@stephxian 

 I will be interested to see if that's possible.

 

However, as a person who spent years as director of a major database for a major corporation, I am very concerned about what DP/IT professionals call data integrity. This broad label can cover many things, but certainly includes saving accurate records of transactions, including transactions that lead to calculations. To take an example from another field, you would not want to enter a figure that shows a person's salary and have it immediately (in the same field) converted to their take-home pay, even though you've verified that the calculation process of deducting, say, taxes and benefits works as desired.  You want to keep the original entry as part of the history.

 

It may be that in your application, that data integrity history doesn't matter, that it's more of an ephemeral thing of temporary meaning. 

 

That, though, is the background reason for my resistance to your desire. Even if it can be accomplished, I don't think it's a good idea, a good practice, when it comes to designing a spreadsheet (or any other DP process). There are too many potential downstream unintended side effects.