Solved: Calculate the cell type that value to calculate

[marreco]

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.
[VBA]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
[/VBA]

[Bob Phillips]

[vba]
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[/vba]

[marreco]

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]

You would change

[vba]
Me.Cells(Target.Row, "C").Value[/vba]

to

[vba]
Me.Cells(Target.Row, "B").Value[/vba]

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]

I had tried so the result remains in C2

Thank you!

[Bob Phillips]

Impossible!

[marreco]

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


[VBA]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[/VBA]

[Bob Phillips]

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]

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.
I could not understand.

[Aussiebear]

What Bob has suggested, is that you enclose the active part of the code with EnableEvents code, similar to the following

[vba]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[/vba]

[marreco]

Excellent, you guys are awesome.

I loved the genius of you deal with Excel and VBA.

Thank you very much!

thanks to two geniuses excel, thanks!



For some reason I can not put it as solved if someone can do I'm very grateful!

[Aussiebear]

thanks to two geniuses excel, thanks!

All credit should go to Bob, I just clarified his solution.