vlookup  
Author Message
AndersBank





PostPosted: Visual Basic for Applications (VBA), vlookup Top

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  
 
 
Andy Pope





PostPosted: Visual Basic for Applications (VBA), vlookup Top

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)



 
 
Cindy Meister





PostPosted: Visual Basic for Applications (VBA), vlookup Top

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)




 
 
ADG





PostPosted: Visual Basic for Applications (VBA), vlookup Top

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


 
 
Figo Fei - MSFT





PostPosted: Visual Basic for Applications (VBA), vlookup Top

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



 
 
Peter Mo.





PostPosted: Visual Basic for Applications (VBA), vlookup Top

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.


 
 
Namrata Prashar





PostPosted: Visual Basic for Applications (VBA), vlookup Top

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



 
 
Andy Pope





PostPosted: Visual Basic for Applications (VBA), vlookup Top

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.


 
 
FinAnalyst





PostPosted: Visual Basic for Applications (VBA), vlookup Top

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.


 
 
Namrata Prashar





PostPosted: Visual Basic for Applications (VBA), vlookup Top

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



 
 
Namrata Prashar





PostPosted: Visual Basic for Applications (VBA), vlookup Top

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



 
 
Andy Pope





PostPosted: Visual Basic for Applications (VBA), vlookup Top

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")






 
 
Namrata Prashar





PostPosted: Visual Basic for Applications (VBA), vlookup Top

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



 
 
Andy Pope





PostPosted: Visual Basic for Applications (VBA), vlookup Top

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


 
 
Namrata Prashar





PostPosted: Visual Basic for Applications (VBA), vlookup Top

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.

 
 
Andy Pope





PostPosted: Visual Basic for Applications (VBA), vlookup Top

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


 
 
Namrata Prashar





PostPosted: Visual Basic for Applications (VBA), vlookup Top

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

 
 
Andy Pope





PostPosted: Visual Basic for Applications (VBA), vlookup Top

I'm not sure what to suggest next.

Any chance of emailing an example set of workbooks


 
 
Namrata Prashar





PostPosted: Visual Basic for Applications (VBA), vlookup Top

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