Hi

I would do it like this:

Private Sub ReadStationDatum(t As Range, Stashn As String, col As Integer)
Application.ScreenUpdating = False
Set wb = Workbooks.Open(Filename = "C:\path value\\LU\stations.xls")
Set sh = wb.Sheets("Sheet1")
Set stations = sh.Range("A1:AL280")
t.Value = Application.VLookup(Stashn, stations, col, False)
wb.Close
Application.ScreenUpdating = True
End Sub

Regards,
Per

"Walter Briscoe" skrev i meddelelsen
...
I run Excel 2003 (11.5612.5606)
I have a file from which I use VLOOKUP to grab single pieces of data.

I use code something like this:
Const stations = "'C:\path value\[stations.xls]Sheet1'!R1C1:R280C38"

Private Sub ReadStationDatum(t As Range,Stashn As String,Col As Integer)
t.FormulaR1C1 = _
"=VLOOKUP(""" & Stashn & """, " & stations & "," & Col & ",FALSE)"
t.Value = t.FormulaR1C1
End Sub

I don't like this code as it writes twice to a cell.

I came up with a half-way house which also works using the following:

Dim stat As Range

Private Sub ReadStationDatum(t As Range,Stashn As String,Col As Integer)
t.Value = Application.VLookup(Stashn, stat, Col, False)
End Sub

Public Sub TopLevelSubroutine()
Dim here As String

here = ActiveWorkbook.name
Workbooks.Open Filename:="C:\path value\\LU\stations.xls"
Set stat = Range("A1:AL280")
Windows(here).Activate
...
End Sub

I don't like that solution because it explicitly opens my lookup file
and adds to code complexity but the following gets a 2015 error as I
feed a string representing a range rather than a range to
Application.VLookup

Const stations = "'C:\path value\[stations.xls]Sheet1'!R1C1:R280C38"

Private Sub ReadStationDatum(t As Range,Stashn As String,Col As Integer)
t.Value = Application.VLookup(Stashn, stations, Col, False)
End Sub

Range(stations) results in an error 1004.

Is it possible to do the conversion directly?
--
Walter Briscoe