try this

Sub loopws_vlookup()
Dim arr() As Variant
Dim strName As Variant
Dim ws As Worksheet
myarray = Array("July", "Sheet13")
mysum = 0
For Each strName In myarray
mysum = mysum + _
Application.VLookup([b1], _
Sheets(strName).Range("f9:g11"), 2, 0)
Next strName
MsgBox mysum
End Sub


"herman" wrote in message
m...
Good morning,
Could you help me with the following problem ?
In cell A2 of sheet "TOTALS" I want to add up the results of a vlookup
funtion across multiple worksheets.
Thus A2 = vlookup(A1;sheetB!$C:$H;false)+
vlookup(A1;sheetC!$C:$H;false) + vlookup(A1;sheetD!$C:$H;false)
+....... + vlookup(A1;sheetX!$C:$H;false).
X is variable, meaning that sheets may be added or deleted.
Note that the arguments remain constant except for the sheets where
the values are to be looked up.
I don't think this can be handled with any combination of worksheet
functions. Is that right ?
Can anyone help me out here with the most efficient code ?

Thank you very much in advance.
Herman