Solved: Change code to Private Sub Worksheet_Change (ByVal Target As Range)

[marreco]

Hi.
I need to change code Private Sub Worksheet_Change (ByVal Target As Range)

each time a data is added in column 'B' code will fire

can anyone help me
Option Explicit
Sub Insert_Formula()
Dim iRow As Long
With Worksheets("FIN")
iRow = .Cells(.Rows.Count, "B").End(xlUp).Row
With .Range("A2", .Cells(iRow, "A"))
.Formula = "=index(BD!$A$2:$A$2000, match(FIN!B2, BD!$B$2:$B$2000, 0))"
.Value = .Value
End With
End With
End Sub

Thank you!!!

[patel]

It's not clear for me what you want

[marreco]

Hi.
I would like to leave this code automatically!

exemple
Private Sub Worksheet_Change (ByVal Target As Range)
Dim iRow As Long
With Worksheets("FIN")
iRow = .Cells(.Rows.Count, "B").End(xlUp).Row
With .Range("A2", .Cells(iRow, "A"))
.Formula = "=index(BD!$A$2:$A$2000, match(FIN!B2, BD!$B$2:$B$2000, 0))"
.Value = .Value
End With
End With
End Sub

[patel]

do you want help ? help me to understand

[marreco]

Hi.

I want to use the code so automatically.

instead of Sub ('Sub Insert_Formula ()'), I want to use ('Private Sub Worksheet_Change (ByVal Target As Range)').

[patel]

you're repeating the same thing, describe me your goal, I don't want see your code

[jolivanes]

I think (???????????)
After the Dim statements, add

If Target.Column = 2 Then


'Your code

Before the End Sub statement, add "End If"

[marreco]

jolivanes hello, I have not tried because I can not build the code based on your tip.

I'll be grateful if someone could help me!

[jolivanes]

Try this. It goes in the Code Sheet where you are changing Column B


Private Sub Worksheet_Change (ByVal Target As Range)
Dim iRow As Long
If Target.Column = 2 Then
With Worksheets("FIN")
iRow = .Cells(.Rows.Count, "B").End(xlUp).Row
With .Range("A2", .Cells(iRow, "A"))
.Formula = "=index(BD!$A$2:$A$2000, match(FIN!B2, BD!$B$2:$B$2000, 0))"
.Value = .Value
End With
End With
End If
End Sub

[Aussiebear]

Try the following:
Private Sub WorkSheet_Change(ByVal Target As Range)
Dim iRow As Long
With Worksheets("FIN")
If Target.Columns = 2 Then
iRow = .Cells(.Rows.Count, "B").End(xlUp).Row
With .Range("A2", .Cells(iRow, "A"))
.Formula = "=Index(BD!$A$2:$A$2000, Match(FIN!B2, BD!$B$2:$B$2000, 0))"
.Value = .Value
End With
End If
End With
End Sub

[Aflatoon]

Perhaps you mean:
Private Sub WorkSheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B:B")) Is Nothing Then
On Error Resume Next
Application.EnableEvents = False
With Intersect(Target, Range("B:B")).Offset(0, -1)
.FormulaR1C1 = "=Index(BD!R2C1:R2000C1,Match(RC[1],BD!R2C2:R2000C2, 0))"
.Value = .Value
End With
Application.EnableEvents = True
End If
End Sub

[marreco]

Aflatoon, that was a great answer, worthy of an expert in Excel and VBA.:clap:
I'm very happy for helping me!:rotlaugh:
thank you very much!!

[Aussiebear]

I'm not surprised. After all it was Aflatoon and he does have high standards.