VLOOKUP with macro

[xam99]

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

Please Login or Register  to view this content.

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

[Pepe Le Mokko]

If you mean extending the formula when you insert a row, no macro needed
Make your range an Excel Table, and any added row will create the formula automatically

[xam99]

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

[xam99]

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

Please Login or Register  to view this content.

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

[jindon]

Do you mean like this?

Please Login or Register  to view this content.

[xam99]

In L7:L31 there is nothing

[jindon]

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.

[xam99]

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

[jindon]

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.

[xam99]

tried several times in B7: B31 nothing comes out

[jindon]

No result, no error?

Don't know why...

try change to

Please Login or Register  to view this content.

[jindon]

I wonder if Events are disabled.
Try the attached.

[xam99]

I don't know nothing comes out of me

[jindon]

See my previous post.

[xam99]

tried # 12 doesn't work on me

[jindon]

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

Please Login or Register  to view this content.

[xam99]

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

[jindon]

Yeah, it should...

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

[xam99]

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?

[jindon]

5 should be 6

Please Login or Register  to view this content.

[xam99]

Thanks you jindon

[AliGW]

Administrative Note:

Unfortunately, this is a duplicate thread, and you are allowed only ONE thread per issue here.

Please see Forum Rule #5 about thread duplication.

I am closing this thread, but you may continue here in the original thread: https://www.excelforum.com/excel-pro...e-vlookup.html