Vlookup in VBA

[Gutierrez]

Hello again ... I have been here on TT for just 2 days and have to say its been great ... but i need your help once again.

Problem:

I need to program a vlookup in code ... currrently have it in a workbook referencing another workbook ... what I need is to run the vlookup and then pass the values to the current workbook ... I hope I am being clear.

I am new to VBA ... so any code that you could supply would be useful.

 

[SkipVought]

Hi,

vValue = Application.VLOOKUP(TheLookupValue, SomeWB.SomeSH.SomeRANGE, intLookupOffset, FALSE)

example

dim vValue as variant
vValue = Application.VLOOKUP(sName, Workbooks(1).Worksheets(1).Range("A2:Z999"), iCol, FALSE)

Skip,

_[red][/red]

 

[Gutierrez]

Hey Skip ... with that code will that do a vlookup for an entire column??

for instance the lookup value is in a1,a2,a3,a4 and so forth .. and i would like the results to be posted in column ax

 

[SkipVought]

"with that code will that do a vlookup for an entire column??" Why are you asking me a question that you could answer yourself?

Why are you doing this in VBA?

Why not just code the function on the sheet?

Skip,

_[red][/red]

 

[Gutierrez]

the sheet is a template used for reporting ... so it cannot contain a reference to another sheet. thus the need to do this in vba ...

 

[SkipVought]

I don't understand why a template cannot reference another workbook.

You might want to consider using MS Query to reference another workbook and return a resultset, if you don't want to mess with worksheet reference links.

Skip,

_[red][/red]

 

[Gutierrez]

The only tool I have available to me is Excel ... so it must be written in VBA ... I tried the code you provided .. and its not working for me ... here is what i have ... i keep getting the error : subscript out of range.

vValue = Application.vLookup(newMonthData.Sheets("ed501531.2889").Range("A1"), PrevMonth.Sheets("ed501531.2889").Range("A:CC"), 36, False)

 

[SkipVought]

1. Your referenced workbooks must be open.

2. Your workbooks; have you set references to newMonthData & PrevMonth

vValue = Application.vLookup(newMonthData.Sheets("ed501531.2889").Range("A1"), PrevMonth.Sheets("ed501531.2889").Range("A:CC"), 36, False)

Skip,

_[red][/red]

 

[Gutierrez]

this is the complete code:

Dim vValue As Variant
Dim PrevMonth As Workbook
Dim newMonthData As Workbook
Set PrevMonth = Workbooks.Open("C:\Felix\ED501531.2889.xls", 3, , 2)
Set newMonthData = Workbooks.Open("c:\Felix\tempMonthData.xls", 3, , 2)

vValue = Application.vLookup(newMonthData.Sheets("ed501531.2889").Range("A1"), PrevMonth.Sheets("ed501531.2889").Range("A:CC"), 36, False)
End Sub

 

[SkipVought]

FYI:

"The only tool I have available to me is Excel"

MS Query is delivered in Excel, accessed via Data/Get External Data/New Database Query -- Excel Files -- YOUR TARGET WORKBOOK....

" ... so it must be written in VBA"

It ought to be the BEST solution, not necessarily written in VB code.

Skip,

_[red][/red]

 

[Gutierrez]

Unfortunately ... the powers that be .. would liek to see it in VBA .. they feel its easier to maintain ... and since they pay the bills ... then its VBA that i will give them ... that being said ... do you see any reason my code should not work?

 

[SkipVought]

"the powers that be", unless they are technically competent to make such a decision, ought to leave it up to a competent technician.

That's like the guy who hires a carpenter to install cabinets, specifying that the work must be done with a power screwdriver, and no other kind of screwdriver can be used.

RIDICULOUS!

The "subscript out of range" error is USUALLY an incorrect name for an object that your trying to reference. I'd guess that it's one of your Sheet Names, if it's erroring on the VLOOKUP statement.

Here's something else I have noticed -- you have similar data in separate workbook/worksheets. This makes data acquisition/analysis many time harder than if it were organized and structured into one or more relations (tables or related data). NewMonth and PrevMonth are the SAME type of data and ought to be stored in a common table.

http://www.alphabetcitydataworks.com/FundamentalsOfRelationalDatabaseDesign.doc

This is a bad practice and it will continue to bit you unless you take time and effort to employ best and accepted database practices.

Skip,

_[red][/red]

 

[firefytr]

You should start using..

Application.WorksheetFunction.Vlookup

.. instead of ..

Application.Vlookup

It may work, but it may not in all future versions.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci