I have a series of workbooks for the stocks I follow, each named with the stock symbol, e.g. Abbot Labs covered by ABT.xlsm. Each workbook has a number of indicators that it calculates and puts in a range named for the indicator, e.g. the indicator "Trend" (whose value is either "Up" or "Down") is in a single cell named "Trend", which I can then refer to elsewhere as "ABT.xlsm!Trend"

I have a summary workbook (shown in simplified form below) in which I'd like to insert the current value, for each stock, of these indicators.

TrendValueInsert workbook.png

The macro I've written aims to go to each line in this summary, capture the symbol in column A, and concatenate it with the rest of the file name (i.e. ".xlsm!Trend"), retrieve that value, and post it in column B. It hangs at the line noted:


Sub TrendValueInsert()


Dim LineNum As Integer
Dim CurrentSymbol As String

Sheets("Main").Select
Range("A3").Select

For LineNum = 3 To 500
If ActiveCell.Value = "end" Then
Range("A2").Select
Exit Sub
End If

Let CurrentSymbol = Range("a" & LineNum).Value
ActiveCell.Offset(0, 1).Select
Let Selection.Value = Range(CurrentSymbol & ".xlsm!Trend").Value 'HANGS HERE
Range("a" & LineNum).Select

ActiveCell.Offset(1, 0).Select

Next LineNum


End Sub

And returns this error:

Run-time error "1004':

Method 'Range' of object '_Global' failed

The problem seems to be about the concatenation of CurrentSymbol and ".xlsm!Trend", which I'd like to sum to, e.g., ABT.xlsm!Trend, to be able to insert the value of that range in cell B5.

Any help would be greatly appreciated.

Oscar