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.