Solved Excel formulas and value in same cell

January 29, 2015 at 09:25:50
Specs: Windows 7
I want to multiply the "given number" in Cell C4 to the number in Cell G8, and have the result in Cell G8
....but I want to use a range G8:G28, L8:L28, and M8:M28 and still reference/use the number in A1 for all. G8:G28 will have different values as I enter them. Here is what I have for 1 cell, how can I apply it to a range of cells from G8:G28, L, and M?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$8" Then
'disable events
Application.EnableEvents = False
'multiply C4 value to G8 and save in G8
Range("G8").Value = Range("G8").Value _
* Range("C4").Value
End If
'reenable events
Application.EnableEvents = True
Exit Sub
'error handler
ErrHnd:
Err.Clear
'reenable events
Application.EnableEvents = True
End Sub

message edited by DitchAFOL


See More: Excel formulas and value in same cell

Report •


#1
January 30, 2015 at 04:09:54
First, a posting tip...

Please click on the blue line at the end of this post and read the instructions on how to post VBA code so that it is easier for us to read. Then please edit/repost your code.

As for your question, you mention A1 in your text yet I don't see any reference to A1 in your code. Could you explain how A1 is involved?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
January 30, 2015 at 05:30:09
Sorry A1 was suppose to be C4. I want to multiply the "given number" in Cell C4 to the number in Cell G8, and have the result in Cell G8
....but I want to use a range G8:G28, L8:L28, and M8:M28 and still reference/use the number in C4 for all. G8:G28 will have different values as I enter them. Here is what I have for 1 cell, how can I apply it to a range of cells from G8:G28, L8:L28, and M8:M28?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$8" Then
    'disable events
    Application.EnableEvents = False
    'multiply C4 value to G8 and save in G8
    Range("G8").Value = Range("G8").Value _
        * Range("C4").Value
End If
'reenable events
Application.EnableEvents = True
Exit Sub
'error handler
ErrHnd:
Err.Clear
'reenable events
Application.EnableEvents = True
End Sub

message edited by DitchAFOL


Report •

#3
January 30, 2015 at 10:31:35
✔ Best Answer
The first thing you need to do is determine if your Target cell is within your desired ranges, which is done with a Logical operator and the Intersect method:

   If Not Intersect(Target, Range("G8:G28, L8:L28, M8:M28")) Is Nothing Then

Then instead of hard-coding the reference to your Target cell (e.g. $G$8) reference the Target and let VBA do all the work.

Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if Target cell is within desired ranges.
   If Not Intersect(Target, Range("G8:G28, L8:L28, M8:M28")) Is Nothing Then
    'disable events
      Application.EnableEvents = False
       'multiply Target by C4, save in Target
         Target.Value = Target.Value * Range("C4").Value
   End If
'reenable events
 Application.EnableEvents = True
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
January 30, 2015 at 11:02:13
Thanks a ton for the help! Works great

Report •


Ask Question