Using VLookup in VBA code with variable range

[Tommy]

I want to implement a vlookup in my code that will return the contents
of a cell to me within a dynamic range where the number of rows is
variable. My code thus far:


Sub IdenticalMinLimits()

Dim Result
Dim PHDRange

Dim CellValuePHD

Dim PHDResult

' Fetch min value from PHD data sheet via a VLOOKUP

Windows("PHD_XANS_DATA_SORT.xls").Activate
Worksheets("PHD").Activate

CellValuePHD = Workbooks("PHD_XANS_SOL_Comparison").Sheets("Day
1").Range("S7").Value

PHDRange = Range(Cells(4, 1), Cells(4, 15).End(xlDown))

Windows("PHD_XANS_SOL_Comparison").Activate
Worksheets("Day 1").Activate

PHDResult = Application.VLookup(CellValuePHD, PHDRange, 0)

MsgBox (PHDResult)

End Sub


In the above code, CellValuePHD returns the correct value for me, so I
knwo that bit is working. The error I get is a type mismatch error on
the line:

PHDResult = Application.VLookup(CellValuePHD, PHDRange, 0)

I believe the problem is that I am not defining my range correctly and
am not passing the variable containing the range to the vlookup
correctly. Any ideas on a solution?

Regards,

Tom

 

[Jim Rech]

It's best to dim as specific types. If you had your error would be more
apparent:

Dim PHDRange As Range

You must "Set" a range (or any object):

Set PHDRange = Range(Cells(4, 1), Cells(4, 15).End(xlDown))

No parens unless you are assigning to a variable (fyi):

MsgBox PHDResult

--
Jim
|I want to implement a vlookup in my code that will return the contents
| of a cell to me within a dynamic range where the number of rows is
| variable. My code thus far:
|
|
| Sub IdenticalMinLimits()
|
| Dim Result
| Dim PHDRange
|
| Dim CellValuePHD
|
| Dim PHDResult
|
| ' Fetch min value from PHD data sheet via a VLOOKUP
|
| Windows("PHD_XANS_DATA_SORT.xls").Activate
| Worksheets("PHD").Activate
|
| CellValuePHD = Workbooks("PHD_XANS_SOL_Comparison").Sheets("Day
| 1").Range("S7").Value
|
| PHDRange = Range(Cells(4, 1), Cells(4, 15).End(xlDown))
|
| Windows("PHD_XANS_SOL_Comparison").Activate
| Worksheets("Day 1").Activate
|
| PHDResult = Application.VLookup(CellValuePHD, PHDRange, 0)
|
| MsgBox (PHDResult)
|
| End Sub
|
|
| In the above code, CellValuePHD returns the correct value for me, so I
| knwo that bit is working. The error I get is a type mismatch error on
| the line:
|
| PHDResult = Application.VLookup(CellValuePHD, PHDRange, 0)
|
| I believe the problem is that I am not defining my range correctly and
| am not passing the variable containing the range to the vlookup
| correctly. Any ideas on a solution?
|
| Regards,
|
| Tom
|

 

[Tommy]

I have changed my code accordingly but I still have a type mismatch
(Runtime Error '13'), which comes on this line:

PHDResult = Application.VLookup(CellValuePHD, Range(Cells(4, 1),
Cells(4, 15).End(xlDown)), 0)

Note that i declared PHDResult as a string.

 

[Ronald Dodge]

VBA doesn't use the worksheet functions directly like that, though there is
a group within VBA known as WorksheetFunctions, so you could use something
like:

Application.WorksheetFunction.VLookup(....)

I just put in the dots as you would use your arguments in place of those
dots.

A few more tips on how to use VBA coding to help aid your learning curve.

As a general rule, don't use Active<Object> or Activate method as these
things in general present issues that you will run into later on down the
road as you get into coding. These items do have their purposes, but should
be rarely used in nature.

Prequalify your your objects and variables to avoid ambigities. I.e.
Thisworkbook.Worksheets("PHD").Cells(4,1)

For objects being reference to more than once, generally wouldn't be a bad
idea to assign to a variable.

When naming variables, first 3 characters should be lower case and indicate
what type of variable it is, then the rest of the variable name should
indicate what it is that the variable is containing using like proper naming
casing style. This is done for readability purposes. For example, is the
variable "CellValuePHD" a numeric value or is it a string value? If it's a
numeric value, what type of numeric value is it? Date/Time counts as
numeric with a "Double" data type. In VBA, there is a Date data type, but
it's core numeric value is of "Double" data type. Therefore, in VBA, you
can use either "Double" or "Date" data types for date/time data values.

Explicitly declare your variables, and preferably to a particular data type
or object type to avoid possible mistypes and to use memory more
efficiently. Also, when you are done using the object variables, close them
out or set them to "NOTHING".

Object variables must use the "Set" keyword in front of the variable name
when being set. All other variables implicitly uses the "Let" keyword,
which can be, but isn't required to be put in front of the variable name
when it is being let to be the same value as the result of the expression on
the right side of the equation.

I know in the example below, some may say I am going overboard on
prequalifying, but it's to avoid possible conflicts as other COM objects may
be referenced down the road, which then can interfere with the various
variables, if proper qualifications isn't put into place, and the COM(s) has
the same names for references. Left, Top, Width, and Height are 4 such
common properties that can mean to so many different things. This generally
isn't as much of an issue with Excel, but has been a very big issue using
VBA in Access. That's why I do it with as much of my code as I reasonably
can. There are only certain ones that I don't prequalify, which for VBA is
mostly dealing with things that's hard coded into VBA such as the data
conversion functions, as they seem to error out with the prequalifications.
I'm going to assume that the CellValuePHD is a numeric value.

'----------START OF CODE----------

'Module Settings
Option Explicit

Sub IdenticalMinLimits()

'Method Variable Declaration
Dim wbkPhdXansDataSort As Excel.Workbook, wbkPhdXansSolCompare As
Excel.Workbook
Dim wshPhdDay1 as Excel.Worksheet, wshPHD as Excel.Worksheet
Dim rngPhdDataSort As Excel.Range, lngPhdDay1 as Long, lngPhdResult as
Long

'Method Variable Initialization
Set wbkPhdXansSolCompare = Workbooks("PHD_XANS_SOL_Comparison.xls")
Set wshPhdDay1 = wbkPhdXansSolCompare.Worksheets("Day1")
lngPhdDay1 = wshPhdDay1.Range("S7").Value2

Set wbkPhdXansDataSort = Workbooks("PHD_XANS_DATA_SORT.xls")
Set wshPHD = wbkPhdXansDataSort.Worksheets("PHD")
Set rngPhdDataSort = wshPHD.Range(Cells(4,1).Address(False, False, xlA1,
False), _
Cells(4,15).End(xlDown).Address(False, False, xlA1, False))

'Perform search
On Error Resume Next
lngPhdResult = Application.WorksheetFunction.VLookup(lngPhdDay1,
rngPhdDataSort, 0)
If Err.Number <> 0 Then
Err.Clear
MsgBox "VLookup could not locate a proper value to return.", 48
Else
MsgBox CStr(lngPhdResult), 48
End If

'Clean Up Process
Set rngPhdDataSort = Nothing
Set wshPHD = Nothing
Set wbkPhdXansDataSort = Nothing

Set wshPhdDay1 = Nothing
Set wbkPhdXansSolCompare = Nothing

End Sub


'-----------END OF CODE-----------

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

 

[Tommy]

Many thanks for your informative post, some very helpful tips. With
regards to the code you posted, i modified the vlookup call to return
the value in the 15th column (change 0 to 15). This should return a
value of -10 from my table, but the code runs the msgbox: "VLookup
could not locate a proper value to return." Any suggestions?? The
vlookup should basically look at the cell in column A of the selected
range and return the value in column O.

 

[Ronald Dodge]

Do you have the first column of the table in ascending order?

Also, try replacing that line with the following:

lngPhdResult = Application.WorksheetFunction.VLookup(lngPhdDay1,
rngPhdDataSort.Address(False, False, xlA1, False), 0)

This may be cause it may be looking for the Range reference in "String"
format rather than an actual range object.


--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

 

[Dave Peterson]

You're not passing 4 arguments to the =vlookup() function.

phdresult = application.vlookup(value, rng, columntobereturned, 0_or_False)

 

[Ronald Dodge]

The 4th argument is optional. If it's omitted, then it's assuming the value
of "True", which in VBA code would be any value other than 0, though the
"True" boolean value contains the value of -1.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

 

[Dave Peterson]

Yes, the 4th argument is optional, but the 3rd argument can't be 0. It sure
looks like the OP dropped the 3rd argument and wanted that 4th argument to be 0
(or false).

 

[Ronald Dodge]

Maybe as I should have caught that myself, but it's been a long time since I
used the VLOOKUP, which I use MATCH mostly instead when using these types of
functions, though more so on the spreadsheet side, not so much in VBA.
Therefore, I didn't catch it right away. However, he did mention in a
response to one of my responses, he changed that value from "0" to "15",
which in his example to refer to the "O" column with the lookup column being
"A".

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

 

[Ronald Dodge]

My initial thinking on it, which is probably why I didn't catch it at first,
he was using that "0" to catch which value it was picking up, like using
that column as an offset to the right.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

 

[Dave Peterson]

In the code you posted, you declared PHDResult as a variant with a line like:

Dim PHDResult

If you're going to use application.vlookup() (not
application.worksheetfunction.vlookup()), then you'll want to make sure
PHDResult is a variant (not string, not long...)

Then you can test the results of the =vlookup() with:

phdresult = application.vlookup(...)
if iserror(phdresult) then
msgbox "it wasn't found" 'same as #n/a error
else
msgbox phdresult
end if

===
Can you get the formula to work if you put it in a cell in a worksheet?

If you cannot, you may want to look at Debra Dalgleish's site:
http://contextures.com/xlFunctions02.html#Trouble

Maybe your values aren't what you think they are.

 

[Tommy]

thanks for all your suggestions. I have implemented dave's suggestions
into my code but I still not get a value returned. The code now:

Sub IdenticalMinLimits()

Dim PHDRange As Range

Dim CellValuePHD

Dim PHDResult As Variant

' Fetch min value from PHD data sheet via a VLOOKUP

Windows("PHD_XANS_DATA_SORT.xls").Activate
Worksheets("PHD").Activate

CellValuePHD = Workbooks("PHD_XANS_SOL_Comparison").Sheets("Day
1").Range("S7").Value

Set PHDRange = Range(Cells(4, 1), _
Cells(400, 15))

PHDResult = Application.VLookup(CellValuePHD, PHDRange, 15, False)
If IsError(PHDResult) Then
MsgBox "it wasn't found" 'same as #n/a error
Else
MsgBox PHDResult
End If

Windows("PHD_XANS_SOL_Comparison").Activate
Worksheets("Day 1").Activate


End Sub

No luck using this so far!

 

[Ronald Dodge]

Have you tried converting the range object to a range address in string
format as I had previously suggested?

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

 

[Dave Peterson]

Did you do this?

===
Can you get the formula to work if you put it in a cell in a worksheet?

If you cannot, you may want to look at Debra Dalgleish's site:
http://contextures.com/xlFunctions02.html#Trouble

Maybe your values aren't what you think they are.

 

[Tommy]

Ok I will try that. I feel it may be more helpful to post exactly what
I'm trying to do in order to get a quicker result. I have two
workbooks, PHD_XANS_DATA_SORT.xls and PHD_XANS_SOL_Comparison.xls .
PHD_XANS_DATA_SORT.xls has 3 sheets, one called PHD which stores data
and XANS which also stores data. FYI, PHD and XANS are two databases
that contain similar data that I am comparing to find any
discrepancies. PHD_XANS_SOL_Comparison.xls is a workbook used to
perform calculations based on the data e.g. comparing whether some
data exists in PHD but not in XANS.

Data is input into the database on a daily basis, and I will therefore
have within my PHD_XANS_SOL_Comparison.xls workbook, sheets for each
day of the week, e.g. a sheet called "Day 1", "Day 2" etc. Common to
each data set is a tagname, stored as a string in a cell. An example
tag might be 01TI518A.PV. It is important to note that each day, a
different number of tags appear, and as such any range that would use
a function such as VLOOKUP must be dynamic as the number of rows in
the range can change.

Within PHD_XANS_SOL_Comparison.xls I have a list of different tags
running through S7:S27 that have been already taken and sorted from
PHD_XANS_DATA_SORT.xls. I would like to be able to run a VLOOKUP on
this tag in the 'PHD' sheet of the PHD_XANS_DATA_SORT.xls workbook
that will look for the first instance of the tag in e.g. S7 and into
U7 return me the value of the 15th column ('O') in the 'PHD' sheet.
The range that is selected in 'PHD' always starts at cell A4 is as
wide as cell O4 (so it is 15 columns wide). The number of rows that it
contains is variable although I suspect that if a defined range was
big enough e.g. 1000 rows, it would easily be able to hold all likely
numbers of rows containing data.

When I have tried to code this into VB, I have often run into type
mismatches using the vlookup function or I have encountered syntax
issues which are likely due to my inexperience with the language (I
have only started coding VB within the last few weeks).

Eventually, then the value returned from the vlookup will be used in
an IF function for data checking.