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

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
"Tommy" wrote in message
ups.com...
|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
|

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.

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

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



Tommy wrote:

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.

--

Dave Peterson

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" wrote in message
...
You're not passing 4 arguments to the =vlookup() function.

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



Tommy wrote:

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.

--

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 wrote:

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" wrote in message
...
You're not passing 4 arguments to the =vlookup() function.

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



Tommy wrote:

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.

--

Dave Peterson

--

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 wrote:

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.

--

Dave Peterson

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!

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 wrote:

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!

--

Dave Peterson

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

"Tommy" wrote in message
ups.com...
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!

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" wrote in message
ups.com...
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

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.

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

"Tommy" wrote in message
oups.com...
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.