How to loop through VBA defined variables

Hi,

I have defined a set of variables 'mydata1', 'mydata2' ... 'mydata35' using the value of a named cell in a closed sheet

I would then like to loop through these variables to complete a repetative bit of code, think it should be possible, but im not sure how?

My code is below, any questions then just ask, thanks.

For i = 1 to 10
    FilePath = ThisWorkbook.Worksheets(2).Range("B" & i).Value    'file paths and names are printed on worksheet 2 already
    FileName = ThisWorkbook.Worksheets(2).Range("A" & i).Value
    JustPath = Left(FilePath, InStrRev(FilePath, "\"))    
    
    '''data location & range to copy
    mydata1 = "='" & JustPath & "[" & FileName & "]Sheet1'!" & "NamedCell"
    mydata2 = "='" & JustPath & "[" & FileName & "]Sheet1'!" & "AnotherNamedCell"
    'etc. etc.

    For j = 1 to 35
        '''link to worksheet
        With ThisWorkbook.Worksheets(1).Cells(i,j)  'I dont want to write out this with loop 35 times
            .Formula = mydata & j                             '<<<<< this is the bit im not sure about
            '''convert formula to text
            .Value = .Value
        End With
    Next
Next

Yeah, I didnt think so, was just to illustrate.

How would I go about using an array in this example, I've not had any experience using them (been meaning to learn

I have successfully defined an array and populated it, however Im getting an error when trying to call the value from the array.

Dim MyData(1 To 35) As String

'code in between

    MyData(1) = "='" & JustPath & "[" & FileName & "]Sheet1'!CellName"
    MyData(2) = "='" & JustPath & "[" & FileName & "]Sheet1'!AnotherCellName"
    etc.

    For j = 1 To 35
        'link to worksheet
        With ThisWorkbook.Worksheets(4).Range("B" & j)
            .Formula = MyData(j)       '<<<< Run time error 1004 here 'application defined or object defined error'
            'convert formula to text
            .Value = .Value
        End With
    Next

Hi..

What are you ultimately trying to do..?

If you post some more details.. (a sample Workbook would be good). perhaps we can help you make it more efficient..

Apo: The objective of the program is to pull information from named cells in many similar workbooks and place it on a worksheet, without having to open the workbooks and copy/paste the info.

nathansav: You were right to question the formula, my code now works.

For some reason when a filepath contains an appostrophe - ' - then it has to be replaced with two apostrophes - '' [NOT "] - when using the file calling technique employed here. e.g. HMO's becomes HMO''s. Does anyone know the reason for this out of interest??

Note: I am calling the files from a mapped network drive, not a local one on the computer.

Also I had to remove the square brackets and didnt need to include the sheet name. For reference my new code is below:

Dim MyData(1 To 35) As String

FilePath = ThisWorkbook.Worksheets(2).Range("B" & i).Value
FileName = ThisWorkbook.Worksheets(2).Range("A" & i).Value
    
JustPath = Left(FilePath, InStrRev(FilePath, "\"))
JustPathNew = Replace(JustPath, "HMO's", "HMO''s")                '<<Adds the apostrophe (') 


MyData(1) = "='" & JustPathNew & FileName & "'!CellName"          '<<Altered, problem was actually here
MyData(2) = "='" & JustPathNew & FileName & "'!AnotherCellName"   '<<Altered, problem was actually here
'etc.

For j = 1 To 35
    'link to worksheet
    With ThisWorkbook.Worksheets(4).Range("B" & j)
        .Formula = MyData(j)       '<<No longer error 1004
        'convert formula to text
        .Value = .Value
    End With
Next

Hi..

Just out of interest.. do you have 34 more lines similar to this:

MyData(1) = "='" & JustPathNew & FileName & "'!CellName"

I mean.. are you populating the MyData array via 35 lines of code or using a loop etc?

apo: Yes there are another 34 lines of similar code. Each element in the MyData array is populated with the value of a certain cell name. These cells sometimes change position in the different workbooks being read and are not in a list (hence they are named for reference) so I cant think of an alternative to cycle through and populate the array with a loop. Any Ideas.

nathansav: Ah ok, makes sense now