View Full Version : Solved: Calculate the cell type that value to calculate
marreco
04-02-2012, 04:24 AM
Hi.
I would like the calculations in column "C" were made in the "B".
I type a value in B2, then B2 will result.
The calculation will be well, when entering a value in any cell in column "B".
will be sought in the spreadsheet "X", the value in column B of this worksheet will be multiplied by a cell in column "A".
See the formula in column "C", to better understand.
I tried this code but it did not work.
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B2:B10")) Is Nothing Then
Exit Sub
Else:
Sheets("Plan1").Range("B2").Value = Sheets("X").Range("B2:B10").Value * Sheets("
Sheets("Plan1").Range("B2").Value = Sheets("X").Range("B2:B10").Value
End If
End Sub
Bob Phillips
04-02-2012, 04:46 AM
Private Sub Worksheet_Change(ByVal Target As Range)
Dim pos As Long
If Not Intersect(Target, Range("A2:B10")) Is Nothing Then
On Error Resume Next
pos = Application.Match(Me.Cells(Target.Row, "A").Value, Sheets("X").Range("A2:A10"), 0)
On Error GoTo 0
If pos > 0 Then
Me.Cells(Target.Row, "C").Value = Me.Cells(Target.Row, "B").Value _
* Sheets("X").Range("B2:B10").Cells(pos, 1).Value
Else
Me.Cells(Target.Row, "C").Value = ""
End If
End If
End Sub
marreco
04-02-2012, 04:59 AM
Hi
XLD, thank you.
I wonder how do I enter in B2, and resultdo, aparcer in B2?
Example.
I typed in B2 the value 10, after giving B2 enter = 120.
Bob Phillips
04-02-2012, 05:02 AM
You would change
Me.Cells(Target.Row, "C").Value
to
Me.Cells(Target.Row, "B").Value
But you would also need to disable events at the start and re-enable at tyhe end, otherwise the code will cascade on and on forever.
marreco
04-02-2012, 05:10 AM
I had tried so the result remains in C2
Thank you!
Bob Phillips
04-02-2012, 05:13 AM
Impossible!
marreco
04-02-2012, 05:22 AM
hi.
I had changed in the wrong place, so I tried but gave error.
This would, if it is giving error!
Error 28
Not enough space to stack
Private Sub Worksheet_Change(ByVal Target As Range)
Dim pos As Long
If Not Intersect(Target, Range("A2:B10")) Is Nothing Then
On Error Resume Next
pos = Application.Match(Me.Cells(Target.Row, "A").Value, Sheets("Números x Nomes").Range("A2:A10"), 0)
On Error GoTo 0
If pos > 0 Then
Me.Cells(Target.Row, "C").Value = Me.Cells(Target.Row, "B").Value _
* Sheets("Números x Nomes").Range("B2:B10").Cells(pos, 1).Value
Else
Me.Cells(Target.Row, "C").Value = ""
End If
End If
End Sub
Bob Phillips
04-02-2012, 07:12 AM
But you would also need to disable events at the start and re-enable at the end, otherwise the code will cascade on and on forever. ... or else you will get an Error 28.
marreco
04-02-2012, 10:38 AM
But you would also need to disable events at the start and re-enable at the end, otherwise the code will cascade on and on forever.
To be honest, I do not how.:dunno
I could not understand.
Aussiebear
04-02-2012, 03:00 PM
What Bob has suggested, is that you enclose the active part of the code with EnableEvents code, similar to the following
Private Sub Worksheet_Change(ByVal Target As Range)
Dim pos As Long
Application.EnableEvents = False
If Not Intersect(Target, Range("A2:B10")) Is Nothing Then
On Error Resume Next
pos = Application.Match(Me.Cells(Target.Row, "A").Value, Sheets("Números x Nomes").Range("A2:A10"), 0)
On Error Goto 0
If pos > 0 Then
Me.Cells(Target.Row, "C").Value = Me.Cells(Target.Row, "B").Value _
* Sheets("Números x Nomes").Range("B2:B10").Cells(pos, 1).Value Else
Me.Cells(Target.Row, "B").Value = ""
End If
End If
Application.EnableEvents = True
End Sub
marreco
04-02-2012, 05:15 PM
Excellent, you guys are awesome.:rofl:
I loved the genius of you deal with Excel and VBA.:clap:
Thank you very much!:thumb
thanks to two geniuses excel, thanks!:bow:
For some reason I can not put it as solved if someone can do I'm very grateful!
Aussiebear
04-02-2012, 10:26 PM
thanks to two geniuses excel, thanks!:bow:
All credit should go to Bob, I just clarified his solution.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.