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
|