VLOOKUP with macro

Hello everybody.
In the attached workbook there is a formula cerca.vert

=SE(L8="";"";SE(VAL.NON.DISP(CERCA.VERT(L8;data_base!$A$2:$B$1500;2;0));"v.n.d.";CERCA.VERT(L8;data_base!$A$2:$B$1500;2;0)))
=IF(L8="","",IF(ISNA(VLOOKUP(L8,data_base!$A$2:$B$1500,2,0)),"v.n.d.",VLOOKUP(L8,data_base!$A$2:$B$1500,2,0)))

is it possible to turn it into a macro at each new insertion in the range L7:L31 ?
Thank you.
xam

Hi peppe le mokko
I don't mean what you say.
I ask to translate the formula into a macro at each insertion

Hi,
i made this macro but it doesn't work

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

'Sub vlookup_alternative()

    Dim data As Long
    Dim Rng As Range
    
    data = Worksheets("S1").Range("L7:L31").Value 'dove scrivo
    
        With Sheets("data_base").Range("A2:A") 'data base dove nome
        
            Set Rng = .Find(What:=data, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
                            
            If Not Rng Is Nothing Then
            
        Sheets("data_base").Cells(Rng.Row, 2).Value = Worksheets("S1").Range("B7:B31").Value '5 colonna nome foglio        
                
                
            End If
            
        End With
        
End Sub

it must work in the range L7:L31 and return the value in B7:B31.
xam

In L7:L31 there is nothing

It uses Change event, like your code, so it only works when you change any cell in L7:L31 (got typo in my code, L2:L31)

Enter any thing in blank cell, so that you will see.

I'm probably doing something wrong.
I did as you say (I hope) but it doesn't change.
Write in B7: B31 the result in cells L7: L31

Try
1) Clear L7:L31 in S1 sheet
2) Enter 123460 in any cell in L7:L31
3) You should see the result in Col.B in S1 sheet.

Above is working here.

tried several times in B7: B31 nothing comes out

I don't know nothing comes out of me

I wonder if Events are disabled.
Try the attached.

See my previous post.

tried # 12 doesn't work on me

I can not test this.
Not sure if you need to change semi-column ";" to a comma "," in the formula.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range
    If Intersect(Target, [L7:L31]) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    For Each r In Intersect(Target, [L7:L31])
        With r.Offset(, -10)
            .Formula = "=SE(L8="""";"""";SE(VAL.NON.DISP(CERCA.VERT(L8;data_base!$A$2:$B$1500;2;0))" & _
                ";""v.n.d."";CERCA.VERT(L8;data_base!$A$2:$B$1500;2;0)))"
            .Value = .Value
        End With
    Next
    Application.EnableEvents = True
End Sub

I shut down and restarted the computer, now everything works.
Thanks jindon

Yeah, it should...

Please don't forget to mark the thread as [SOLVED].

I moved the data_base to test and changed here

r(, -9) = WorksheetFunction.IfError(Application.VLookup(r, _
Sheets("data_base").Columns("D:I"), 5, False), "v.n.d")

but non work, where am i wrong?

5 should be 6

            r(, -9) = WorksheetFunction.IfError(Application.VLookup(r, _
                Sheets("data_base").Columns("D:I"), 6, False), "v.n.d")

Thanks you jindon