Worksheet Function in Excel VBA 1

[Mahathma]

I am trying to use VLookup function in Excel VBA.
Eventhough I use the syntax application.WorksheetFunction.VBA(______)
it throws up an alarm Unable to get the VLookup property of the WorksheetFunction Class.
I was successful in using the same syntax for Max function.
Is it the limitation with Excel that we cannot use functions?
Please help.
Thanks in advance Guru

 

[DaleWatson123321]

Hi Guru,

Using the VLOOKUP function in Excel VBA is possible.

Here are a couple of examples (that work).

The first routine looks up the value from the cell named "num", in the table named "tbl".

Sub Lookup_Number_from_Sheet()
result = Application.WorksheetFunction.VLookup(Range("num&quot, Range("tbl&quot, 2, False)
MsgBox result
End Sub

This second example uses the memory variable "nbr", and looks it up in the range named "tbl".

Sub Lookup_Variable()
nbr = 4
result = Application.WorksheetFunction.VLookup(nbr, Range("tbl&quot, 2, False)
MsgBox result
End Sub

If you haven't yet begun to create and use range names, I STRONGLY recommend that you start. They can be EXTREMELY beneficial.

Method of Assigning a Range Name:
a) Highlight the cell or range-of-cells
b) Hold down the <Control> key and hit <F3>
c) Type the name
d) Hit <Enter>

I hope this helps. Please advise as to how you make out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Mahathma]

Thanks.
I do use range.
The range I use has 5 columns. Each column is made up of merged cells.
When I refer to cell to look up, I did give the actual column count. eg. 31 instead of 5.
Then I unmerged the cells and tried.
But, I still get this message
Thanks
Guru

 

[DaleWatson123321]

Guru,

Thanks for the STAR.

With your description of &quot;merged cells&quot;, and &quot;the actual column count. eg. 31 instead of 5&quot;... I take this to mean that your merged cells are merged &quot;horizontally&quot; - i.e. where you are merging columns (NOT rows).

I've just modified the example I created for your situation, and I now have 55 columns, with two sets of 2 merged columns.

The following variation of my previous example STILL works, EVEN WITHOUT &quot;un-merging&quot;.

Sub Lookup_Variable()
nbr = 4
colm = 3
result = Application.WorksheetFunction.VLookup(nbr, Range(&quot;tbl&quot, colm, False)
MsgBox result
End Sub

Actually, EVEN when the table includes MERGED cells that include BOTH columns and rows, the routine will STILL work. However, with such a table, you would need to have a very &quot;special&quot; situation to want such a &quot;difficult&quot; table to work with.

What makes such a table &quot;difficult&quot;, is that there will be BLANK columns and/or rows for each &quot;merged&quot; set of columns and/or rows. You would THEN need to adjust your &quot;offset&quot; for the column or the row, in order to reference the proper cell containing the data you are looking up.

I hope all of the above makes sense, and that you're able to adjust your table accordingly.

If you still have difficulty, an option would be for you to email me your file (or a scaled down version). I then should be able to &quot;pinpoint&quot; the exact problem.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Mahathma]

Thanks Dale.
I've e-mailed the file.
The sample does not work if the variable is in date format.
It works fine for text or integer.
Thanks again
Guru