vlookup

Finding closest value in a column

I tried using:
x=vlookup(strStartDatumArray(1); rng(1); rng(1); False)

where straStartDatumArray(1) contains the date that I want to find. I want
to find it below the cell rng(1) and I want to return that very value
(rng(1)). rng(1) is a position in an array that is given like this:
rng(k)=Worksheets(varWorksheetInfoArray(0)).Cells.Find(varWorksheetInfoArray(k), LookIn:=xlValues)

If you have any idea how to make my code work I would be most grateful for
your assistance! Thank you very much!

Microsoft ISV Community Center Forums1

Hi

Try a change of tack. Try using the Find command to go to the second instance, then get the value from column B in the found row. Example of finding the secind instance of "Bosch" below:

Columns("A:A").Select
Selection.Find(What:="BOSCH", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Selection.FindNext(After:=ActiveCell).Activate

Hi AndersBank

Since you haven't gotten a response yet, try asking the Excel
specialists in the Excel programming newsgroup, here:

http://msdn.microsoft.com/newsgroups/default.aspx dg=microsoft.public.ex
cel.programming&lang=en&cr=US
<http://msdn.microsoft.com/newsgroups/default.aspx dg=microsoft.public.e
xcel.programming&lang=en&cr=US>

-- Cindy (Word MVP)

Hi,

How about some like this

Dim rngLookupValue As Range
Dim rngtable As Range
Dim lngColIndex As Long
Dim blnRangeLookup As Boolean

Set rngLookupValue = Range("O2")
Set rngtable = Workbooks("Consolidated list of supplier.xls").Worksheets("Sheet3").Range("$A$5:$F$218")
lngColIndex = 6
blnRangeLookup = False

vntResult = Application.WorksheetFunction.VLookup(rngLookupValue, rngtable, lngColIndex, blnRangeLookup)

Hi,

You must give more info about the WorksheetFunction and what you exactly want to do, so we can help you figure it out .

Thank you

thanks Andy for the reply.

i tried writing something similar to what you suggested but i get error. i wrote

Public LookupValue As Range
Public TableArray As Range

Set LookupValue = Sheet3.Range("O2")
Set TableArray = Workbooks(MyPath).Worksheets("Sheet3").Range("$A$5:$F$281") ............... error here

The error is

Run Time error 9

Sub-Script out of range

can you please help

thanks

namrata

Hi

As an alternative if you like WorksheetFunctions you could try

With Range("CPARSdata")
DestCity = WorksheetFunction.VLookup("Line#1 CITY", .Range(.Cells( _
WorksheetFunction.Match( _
"Line#1 CITY", .Range(.Cells(1, 1), .Cells(.Rows.Count, 1)), 0) + 1, _
1), .Cells.SpecialCells(xlCellTypeLastCell)), 2, False)
End With

Regards

Peter Mo.

What does the variable MyPath contain

If you have a file C:\MyFolder\book1.xls open it's name will be book1.xls. It will not include the path.

Namrata,

Is MyPath the name of the workbook, or is it a variable If it is the name of a workbook, then put "MyPath" in quotations.

hello all,

MYPath is a variable which contains the name of the excel file i want to open for vlookup. it is in a different folder and not in the one where the original excel is (the one i am writing macro in).

can you now help me with why this error.

namrata

hello,

the whole code that i have written is as follows

Public LookupValue As Range
Public TableArray As Range
Public MyPath As String

Sub VlookUp_Click()

MyPath = "C:\Documents and Settings\np22331\Desktop\Consolidated list of supplier.xls"

Set LookupValue = Sheet3.Range("O2")
Set TableArray = Workbooks(MyPath).Worksheets("Sheet3").Range("$A$5:$F$281")

End Sub

namrata

If the file is open then you do not need the full path as the name only the filename and extension.

MyPath = "C:\Documents and Settings\np22331\Desktop\Consolidated list of supplier.xls"

MyFile = "Consolidated list of supplier.xls"

Set LookupValue = Sheet3.Range("O2")
Set TableArray = Workbooks(MyFile).Worksheets("Sheet3").Range("$A$5:$F$281")

hi,

i modified the code as below but it gives the following error ..

run time error 1004

unable to get the vlookup property of the worksheet function class

my new code looks something like this.

**************************************************************************

Sub VlookUp_Click()

Dim LookupValue As Range
Dim TableArray As Range
Dim ColIndex As Integer
Dim RangeLookup As Boolean
Dim MyPath As String

MyPath = "C:\Documents and Settings\np22331\Desktop\Consolidated list of supplier.xls"

Workbooks.Open Filename:=MyPath, UpdateLinks:=0, ReadOnly:=True
Set TableArray = Workbooks("Consolidated list of supplier.xls").Worksheets("Sheet3").Range("$A$5:$F$281")
ActiveWorkbook.Close SaveChanges:=False

Set LookupValue = Sheet3.Range("O2")
RangeLookup = False
ColIndex = 6

Sheet3.Cells(2, 16) = Application.WorksheetFunction.VLookup(LookupValue, TableArray, RangeLookup, ColIndex)

End Sub
**************************************************************************************

can somebody please help me with fixing the bug !!

thanks

namrata

What happens if you wait until after the lookup before closing the workbook

Sheet3.Cells(2, 16) = Application.WorksheetFunction.VLookup(LookupValue, TableArray, RangeLookup, ColIndex)

ActiveWorkbook.Close SaveChanges:=False

buddy, the lookup is used in a different excel worksheet and the 'consolidated supplier.xls' is a different file. if i do not close the consolidated.xls there will be again a problem of active worksheet as the macro is not being written in the consolidated.xls... its just being used for the lookup range purpose.

You could fully qualify this reference

Set LookupValue = Sheet3.Range("O2")

The 1004 error will be raised if not match is found. Are you sure the value you are looking for is in the lookup table

yes, the lookup value is there .... when i try to use the vlookup directly in the excel sheet for the same set of values it works fine but it is giving this error here when coded in VBA

I'm not sure what to suggest next.

Any chance of emailing an example set of workbooks

also,

when i replace the code as

Application.WorksheetFunction.VLookup(LookupValue, TableArray, RangeLookup, ColIndex)
the error changes to "can not find the vlookup function in the worksheetfunction class" but if i write

Application.VLookup(LookupValue, TableArray, RangeLookup, ColIndex)

the error changes to "1004:application defined or object defined error"

any clue ....

i have fully qualified the range also as follows

Set LookupValue = ThisWorkbook.Sheets("Data Sheet").Range("O2")

namrata