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