no worries, I have worked it out.
Set chkRange = ActiveSheet.Range("F15:m34")
For A = 1 To 160
myValues(A) = myValues(A) + chkRange(A).Value
Next A
"Neil Eves" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi,
>
> I have a number of workbooks in a folder (number varies) which I have to
> open and add the values of the same range ("F15:M34") in each workbook
> into a summary workbook.
>
> The code below works, but to me appears long winded, is there an easier
> more efficient way of writing this code. This is the first time I have had
> a go at using arrays.
>
> thanks in advance
> Neil
>
> Dim myValues(160) As Long
> Dim MyPath As String
>
> Application.ScreenUpdating = False
> Application.DisplayAlerts = False
> Application.EnableEvents = False
> MyPath = ActiveWorkbook.Path
> On Error Resume Next
> Set wbCodeBook = ThisWorkbook
>
> With Application.FileSearch
> .NewSearch
> .LookIn = MyPath
> .FileType = msoFileTypeExcelWorkbooks
> If .Execute > 0 Then
> For lCount = 2 To .FoundFiles.Count
> Set wbResults =
> Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
>
> For A = 1 To 20
> myValues(A) = myValues(A) +
> ActiveSheet.Range("F" & 14 + A).Value
> Next A
> For B = 21 To 40
> myValues(B) = myValues(B) +
> ActiveSheet.Range("G" & -6 + B).Value
> Next B
> For c = 41 To 60
> myValues(c) = myValues(c) +
> ActiveSheet.Range("H" & -26 + c).Value
> Next c
> For d = 61 To 80
> myValues(d) = myValues(d) +
> ActiveSheet.Range("I" & -46 + d).Value
> Next d
> For e = 81 To 100
> myValues(e) = myValues(e) +
> ActiveSheet.Range("J" & -66 + e).Value
> Next e
> For f = 101 To 120
> myValues(f) = myValues(f) +
> ActiveSheet.Range("K" & -86 + f).Value
> Next f
> For g = 121 To 140
> myValues(g) = myValues(g) +
> ActiveSheet.Range("L" & -106 + g).Value
> Next g
> For h = 141 To 160
> myValues(h) = myValues(h) +
> ActiveSheet.Range("M" & -126 + h).Value
> Next h
>
> 'Close the workbook you opened
> wbResults.Close SaveChanges:=True
>
> Next lCount
> End If
> End With
> On Error GoTo 0
> Application.ScreenUpdating = True
> Application.DisplayAlerts = True
> Application.EnableEvents = True
> For i = 1 To 20
> ActiveSheet.Range("F" & 14 + i).Value = myValues(i)
> Next i
> For j = 21 To 40
> ActiveSheet.Range("g" & -6 + j).Value = myValues(j)
> Next j
> For k = 41 To 60
> ActiveSheet.Range("h" & -26 + k).Value = myValues(k)
> Next k
> For l = 61 To 80
> ActiveSheet.Range("i" & -46 + l).Value = myValues(l)
> Next l
> For m = 81 To 100
> ActiveSheet.Range("j" & -66 + m).Value = myValues(m)
> Next m
> For n = 101 To 120
> ActiveSheet.Range("k" & -86 + n).Value = myValues(n)
> Next n
> For o = 121 To 140
> ActiveSheet.Range("l" & -106 + o).Value = myValues(o)
> Next o
> For p = 141 To 160
> ActiveSheet.Range("m" & -126 + p).Value = myValues(p)
> Next p
|
Similar Threads
