Thread: Help Get This Macro Running in OS X Please
View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
WoodyJI[_2_] WoodyJI[_2_] is offline
external usenet poster
  
Posts: 1
Default Help Get This Macro Running in OS X Please

I have this macro that a friend of mine wrote on a windows machine, and
I can't quite figure out the code changes needed for OS X. For someone
who knows how to do this, I'm sure it would only take a second, but I've
been banging my head against the wall on this thing for too long.

Code:
--------------------
Option Base 1

Sub Data_Compiler()

Dim FileSelected
Dim FileName
Dim CompiledDataArray(1 To 30, 1 To 53) 'Array to store our statistics
Dim Counter1 As Integer

Counter1 = 1

FileSelected = Application.GetOpenFilename("Your Files,*.xls", , "Select Files", , True)
If StrComp(TypeName(FileSelected), "boolean", vbTextCompare) = 0 Then Exit Sub


For Each FileName In FileSelected

Application.DisplayAlerts = False

Workbooks.Open FileName
Cells(1, 1).Select

CompiledDataArray(Counter1, 1) = FileName
CompiledDataArray(Counter1, 2) = Application.WorksheetFunction.Average(Range(Cells( 10, 2), Cells(1884, 2)))
CompiledDataArray(Counter1, 3) = Application.WorksheetFunction.StDev(Range(Cells(10 , 2), Cells(1884, 2)))
CompiledDataArray(Counter1, 4) = Application.WorksheetFunction.Min(Range(Cells(10, 2), Cells(1884, 2)))
CompiledDataArray(Counter1, 5) = Application.WorksheetFunction.Max(Range(Cells(10, 2), Cells(1884, 2)))

CompiledDataArray(Counter1, 6) = Application.WorksheetFunction.Average(Range(Cells( 10, 3), Cells(1884, 3)))
CompiledDataArray(Counter1, 7) = Application.WorksheetFunction.StDev(Range(Cells(10 , 3), Cells(1884, 3)))
CompiledDataArray(Counter1, 8) = Application.WorksheetFunction.Min(Range(Cells(10, 3), Cells(1884, 3)))
CompiledDataArray(Counter1, 9) = Application.WorksheetFunction.Max(Range(Cells(10, 3), Cells(1884, 3)))

CompiledDataArray(Counter1, 10) = Application.WorksheetFunction.Average(Range(Cells( 10, 4), Cells(1884, 4)))
CompiledDataArray(Counter1, 11) = Application.WorksheetFunction.StDev(Range(Cells(10 , 4), Cells(1884, 4)))
CompiledDataArray(Counter1, 12) = Application.WorksheetFunction.Min(Range(Cells(10, 4), Cells(1884, 4)))
CompiledDataArray(Counter1, 13) = Application.WorksheetFunction.Max(Range(Cells(10, 4), Cells(1884, 4)))

CompiledDataArray(Counter1, 14) = Application.WorksheetFunction.Average(Range(Cells( 10, 5), Cells(1884, 5)))
CompiledDataArray(Counter1, 15) = Application.WorksheetFunction.StDev(Range(Cells(10 , 5), Cells(1884, 5)))
CompiledDataArray(Counter1, 16) = Application.WorksheetFunction.Min(Range(Cells(10, 5), Cells(1884, 5)))
CompiledDataArray(Counter1, 17) = Application.WorksheetFunction.Max(Range(Cells(10, 5), Cells(1884, 5)))

CompiledDataArray(Counter1, 18) = Application.WorksheetFunction.Average(Range(Cells( 10, 6), Cells(1884, 6)))
CompiledDataArray(Counter1, 19) = Application.WorksheetFunction.StDev(Range(Cells(10 , 6), Cells(1884, 6)))
CompiledDataArray(Counter1, 20) = Application.WorksheetFunction.Min(Range(Cells(10, 6), Cells(1884, 6)))
CompiledDataArray(Counter1, 21) = Application.WorksheetFunction.Max(Range(Cells(10, 6), Cells(1884, 6)))

CompiledDataArray(Counter1, 22) = Application.WorksheetFunction.Average(Range(Cells( 10, 7), Cells(1884, 7)))
CompiledDataArray(Counter1, 23) = Application.WorksheetFunction.StDev(Range(Cells(10 , 7), Cells(1884, 7)))
CompiledDataArray(Counter1, 24) = Application.WorksheetFunction.Min(Range(Cells(10, 7), Cells(1884, 7)))
CompiledDataArray(Counter1, 25) = Application.WorksheetFunction.Max(Range(Cells(10, 7), Cells(1884, 7)))

CompiledDataArray(Counter1, 26) = Application.WorksheetFunction.Average(Range(Cells( 10, 8), Cells(1884, 8)))
CompiledDataArray(Counter1, 27) = Application.WorksheetFunction.StDev(Range(Cells(10 , 8), Cells(1884, 8)))
CompiledDataArray(Counter1, 28) = Application.WorksheetFunction.Min(Range(Cells(10, 8), Cells(1884, 8)))
CompiledDataArray(Counter1, 29) = Application.WorksheetFunction.Max(Range(Cells(10, 8), Cells(1884, 8)))

CompiledDataArray(Counter1, 30) = Application.WorksheetFunction.Average(Range(Cells( 10, 9), Cells(1884, 9)))
CompiledDataArray(Counter1, 31) = Application.WorksheetFunction.StDev(Range(Cells(10 , 9), Cells(1884, 9)))
CompiledDataArray(Counter1, 32) = Application.WorksheetFunction.Min(Range(Cells(10, 9), Cells(1884, 9)))
CompiledDataArray(Counter1, 33) = Application.WorksheetFunction.Max(Range(Cells(10, 9), Cells(1884, 9)))

CompiledDataArray(Counter1, 34) = Application.WorksheetFunction.Average(Range(Cells( 10, 10), Cells(1884, 10)))
CompiledDataArray(Counter1, 35) = Application.WorksheetFunction.StDev(Range(Cells(10 , 10), Cells(1884, 10)))
CompiledDataArray(Counter1, 36) = Application.WorksheetFunction.Min(Range(Cells(10, 10), Cells(1884, 10)))
CompiledDataArray(Counter1, 37) = Application.WorksheetFunction.Max(Range(Cells(10, 10), Cells(1884, 10)))

CompiledDataArray(Counter1, 38) = Application.WorksheetFunction.Average(Range(Cells( 10, 11), Cells(1884, 11)))
CompiledDataArray(Counter1, 39) = Application.WorksheetFunction.StDev(Range(Cells(10 , 11), Cells(1884, 11)))
CompiledDataArray(Counter1, 40) = Application.WorksheetFunction.Min(Range(Cells(10, 11), Cells(1884, 11)))
CompiledDataArray(Counter1, 41) = Application.WorksheetFunction.Max(Range(Cells(10, 11), Cells(1884, 11)))

CompiledDataArray(Counter1, 42) = Application.WorksheetFunction.Average(Range(Cells( 10, 12), Cells(1884, 12)))
CompiledDataArray(Counter1, 43) = Application.WorksheetFunction.StDev(Range(Cells(10 , 12), Cells(1884, 12)))
CompiledDataArray(Counter1, 44) = Application.WorksheetFunction.Min(Range(Cells(10, 12), Cells(1884, 12)))
CompiledDataArray(Counter1, 45) = Application.WorksheetFunction.Max(Range(Cells(10, 12), Cells(1884, 12)))

CompiledDataArray(Counter1, 46) = Application.WorksheetFunction.Average(Range(Cells( 10, 13), Cells(1884, 13)))
CompiledDataArray(Counter1, 47) = Application.WorksheetFunction.StDev(Range(Cells(10 , 13), Cells(1884, 13)))
CompiledDataArray(Counter1, 48) = Application.WorksheetFunction.Min(Range(Cells(10, 13), Cells(1884, 13)))
CompiledDataArray(Counter1, 49) = Application.WorksheetFunction.Max(Range(Cells(10, 13), Cells(1884, 13)))

CompiledDataArray(Counter1, 50) = Application.WorksheetFunction.Average(Range(Cells( 10, 14), Cells(1884, 14)))
CompiledDataArray(Counter1, 51) = Application.WorksheetFunction.StDev(Range(Cells(10 , 14), Cells(1884, 14)))
CompiledDataArray(Counter1, 52) = Application.WorksheetFunction.Min(Range(Cells(10, 14), Cells(1884, 14)))
CompiledDataArray(Counter1, 53) = Application.WorksheetFunction.Max(Range(Cells(10, 14), Cells(1884, 14)))

Counter1 = Counter1 + 1

ActiveWorkbook.Close savechanges:=False
Next

Workbooks.Add

Cells(1, 1).Select

For j = 1 To 53
For i = 1 To 30
Cells(i, j).Value = CompiledDataArray(i, j)
Next i
Next j

End Sub
--------------------

I can get a "open files" dialogue box to pop up, but I can't figure out
how to make sure that multiselect is enabled. I need to be able to run
this macro on 11 files at once. Thanks so much for your time in
reading this post already!


--
WoodyJI
------------------------------------------------------------------------
WoodyJI's Profile: http://www.excelforum.com/member.php...o&userid=37167
View this thread: http://www.excelforum.com/showthread...hreadid=571904

Reply With QuoteReply With Quote