VLOOKUP using VBA to auto fetch data

**RahulM** · 05-15-2013, 05:17 AM

Dear All,

I am having one Tracking sheet for recording data. I want to fetch some values/ data from other worksheet for some columns.

Identifying ‘Request ID’ particular cells should auto fetch data from other worksheets.

Request ID is one unique number. Therefore, it will lookup the Request ID in another worksheet & gets related cell data it in master database.

I have tried VLOOKUP for this and this is giving correct outcome.

However due to ample number (7000) of rows, while saving it goes to "Not responding" mode.

So can you please give some VBA code so that I can fetch the values for specific columns/ cells.

e.g.
=VLOOKUP(A3,Publisher!$A$3:$M$1048576,11,0)

A3 = Request ID
Publisher!$A$3:$M$1048576 = table_array from Publisher TAB

Best Regards,
Rahul

**dilipandey** · 05-15-2013, 05:28 AM

HI Rahul,

welcome to the forum.
Instead of giving full range "$A$3:$M$1048576", you can use defined name which will auto expand as per data increase.

Regards,
DILIPandey
<click on below * if this helps>

**RahulM** · 05-16-2013, 09:14 AM

Thank you DILIP. That would surely help but I don't know VBA.

**dilipandey** · 05-16-2013, 09:21 AM

Hi RahulM,

No need of VBA here.. just press ctrl + F3 and you'll see name manager where you can define dynamic name which will increase the range reference as per data.

Regards,
DILIPandey
<click on below * if this helps>

**RahulM** · 05-16-2013, 09:31 AM

Dear DILIP,

Thank you for help. It does work. Have already used for data validation field.
However I want to automate using VBA because due to ample number of rows (more that 7000 of rows some time), while saving this file it goes to "Not responding" mode. Believe, using VBA will help resolve this issue.