Tricky find function used to sum columns in sheet

[espenskeie]

Hello

I have a list of tickers. I have loop running through the list and pull the tickernames.

My problem is that I need this tickername to be used to find a match in sheet wsX. If this is column D, I somehow would like to store this column number/name, and then search for next tickername and match.

Finally when the loop is done, I would like to write a formula in cell B5, something like this:

wsX.Range("B5").Formula = "=SUM(G5,D5,AB5,AT5,BX5)"

Here is what I have so far:

Sub AddSelectedStocks()
Dim wb As Workbook, wbX As Workbook
Dim ws As Worksheet, ws1 As Worksheet, ws2 As Worksheet, wsX As Worksheet
Dim colnum As Integer
Dim strPath As String, strFilename As String
Dim lr As Long




Set wb = ThisWorkbook
Set ws = wb.Sheets("Master")
Set ws1 = wb.Sheets("Graphics")


With ws
colnum = .Cells(5, .Columns.Count).End(xlToLeft).Column
End With


For i = 3 To colnum


strPath = ws.Range("C2")
strFilename = ws.Cells(5, i) & ".xlsm"


If Len(Dir(strPath & strFilename)) > 0 Then
    On Error GoTo Lst1:
    Set wbX = Workbooks.Open(strPath & strFilename)
    On Error GoTo 0
    
    Set wbX = Workbooks(strFilename)
    Set wsX = wbX.Sheets(ws.Cells(6, i))
    
    lr = ws.Cells(6, i).End(xlDown).Row
    If Not lr < 7 Then
        For j = 7 To lr
        
        
        '************
        '************
        '************
    
    
    
    wbOBX.Close True
End If


Lst1:
Next i


End Sub

Kind regards
Espen