VBA needed to display original value inside cell.

[nd4spd30]

Hello All,

I'm quite new to VBA and I am in need of a solution. I've spent countless hours trying to figure it out, but can't. Any help would be very much appreciated. Here is what I am trying to do:

I maintain commissions file.

-Column A is the sales reps user ID.
-Column B is the %'s of the commissions.
-Column C is the dollar amounts.

Column C contains various dollar amounts. Since some of the user Id's (sales reps) from Column A are split, those sales reps share a percent of the revenue.

Now here is what I do manually and how I'd like to create a macro:

In column C, each cell is selected manually, and a "=" sign is placed/ inserted before the revenue amount (in the formula bar), then multiplying it by column B, going down the entire column until the data ends. So this is how it looks:

$50.00 becomes =50.00*B2, etc. (clicking and inserting the "=" & multiplication).Doing this process manually displays the result, and the original dollar amounts are displayed when the cell is selected. This is what I'd like to automate.

Is there a way to automate this using a vba code, instead of going to each cell manually and typing in an equal sign and a "*", multiplying the percentages in order to keep the original value contained in the cell when selected?


Any solutions or help would very much be appreciated. It would help expedite the process instead of entering each one manually.

 

[John Davis]

Is something like this what you mean?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr As Long
lr = Cells(Rows.Count, 3).End(xlUp).Row
If Not Intersect(Target, Range("C2:C" & lr)) Is Nothing Then
    MsgBox ActiveCell.Value * Range("B2").Value
End If
End Sub

 

[nd4spd30]

Hi John,

When I try to test the code to see if it works I get a Run-time error '424':

Object required?

 

[nd4spd30]

I'm thinking maybe a vba code where the string with numbers could have a "=" sign inserted before it and also a formula attached where it would multiply it by the percentage in column c. Any suggestions?

 

[Scott Huish]

I'm not sure why that code references ActiveCell rather than Target, but where did you put the code exactly?

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C2:C" & Cells(Rows.Count, 3).End(xlUp).Row)) _
    Is Nothing Then _
    MsgBox Target * Range("B2").Value
End Sub

 

[nd4spd30]

I pasted the code in the vba editor under module 1. Didn't seem to work for me. When I "step into" or press F8 to test the code, I get the error message from my previous post.

 

[Scott Huish]

It shouldn't go in a standard module.
It needs to go in the sheet code for the sheet you want this to happen on.
Take it out of the module.
From your spreadsheet, right click on the sheet tab for where you want this to happen on. Click on View Code. Paste into white area.