Help with Loop

[Noz2k]

So I have a bit of code that looks for a value in other workbooks, sums them and posts the new value into the current workbook.

The number of workbooks needed to retrieve data from however is derived from user entered data in the current workbook. So I think I need to create a loop (or for/next) in order to dictate how many times an action needs performing.

Here is the code that I have at the moment which looks in 2 workbooks.

Sub TestGetValue()
 
    NumLoops = (Range("D2") - Range("D1")) / 7 + 1
 
    WeekSNum = (Range("D1") - Range("G1")) / 7 + 1
 
    p = "C:\\myfilelocation"
    f = "Week_" & WeekSNum & " we_" & Format(Range("D1"), "ddmmyy") & "_ISSUED.xls"
    s = "new cstm summary"
    a = "C6"
 
    V1 = GetValue(p, f, s, a)
 
    p = "C:\\myfilelocation"
    f = "Week_" & WeekSNum + 1 & " we_" & Format(Range("D1") + 7, "ddmmyy") & "_ISSUED.xls"
    s = "summary"
    a = "C6"
 
    V2 = GetValue(p, f, s, a)
 
    Sheets("Sheet2").Range("C5") = WorksheetFunction.Sum(V1, V2)
    End Sub

where NumLoops, is how many workbooks it should look through

 

[Noz2k]

Now obviously the final bit of this code doesn't work. But I think this might explain a little bit better the kind of thing I am looking for

Sub TestGetValue()
 
    NumLoops = (Range("D2") - Range("D1")) / 7 + 1
 
    WeekSNum = (Range("D1") - Range("G1")) / 7 + 1
 
    X = 0
    Y = 0
 
Do Until X > NumLoops
    p = "C:\\myfilelocation"
    f = "Week_" & WeekSNum + X & " we_" & Format(Range("D1"), "ddmmyy") & "_ISSUED.xls"
    s = "summary"
    a = "C6"
 
    X = X + 1
    Y = Y + 7
 
 
 
    V(X) = GetValue(p, f, s, a)
 
  Loop 
    Sheets("Sheet2").Range("C5") = WorksheetFunction.Sum(V1:V(NumLoops))
    End Sub

But I need to somehow store the value for GetValue each time, and then sum them all at the end

 

[Noz2k]

Ok so I sort of solved this.

NumLoops = (Range("D2") - Range("D1")) / 7 + 1
 
    WeekSNum = (Range("D1") - Range("G1")) / 7 + 1
 
    MsgBox NumLoops
 
    X = 0
    Y = 0
 
    Do Until X = NumLoops
    p = "C:\\myfilelocation"
    f = "Week_" & (WeekSNum + X) & " we_" & Format(Range("D1") + Y, "ddmmyy") & "_ISSUED.xls"
    s = "summary"
    a = "C6"
 
    V = GetValue(p, f, s, a)
 
    Sheets("Sheet3").Range("A1").Offset(X, 0) = V
 
    X = X + 1
    Y = Y + 7
 
 
    Loop
 
    MsgBox X
    Sheets("Sheet2").Range("B5") = WorksheetFunction.Sum(Sheets("Sheet3").Range("A1:A" & X))
    End Sub

The problem is now, that I want to basically do the same thing for 100 other cells.

and have no idea how to do that without typing everyone out individually.

Is there a way of possibly creating a lookup table which has the cells to find the values in the other workbooks (value a in the code) in 1 column and the cells in which to post to in the adjacent column.

And then run a macro which repeats the above code for each value of a in the table?

Or can anybody think of another way? My head is starting to hurt with this