Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding an Array
This is similiar to a previous post but I think I have my problem reduced even further. I think all I need here is to set up an array and pass the value of that through my current macro. There are 4 places were the value of the array would need to be inputed (as seen below <--Value From Array--). The values for the array will be on "sheet2" of the "ReportOne.xls" file, in column "E".There will be anywhere from 1 to 4 values there. The data that the autofilter is filtering will be on "sheet1" of "ReportOne.xls". I need to loop through my current macro passing into it in those four places each value from the array
My problem is I don't really know how to use arrays, especially in this manner - I think it is all I need - and this is the only solution I can put together that can come close to figuring out for my problem. Any help on this would be great. This is as far as I can take this alone Sub FindOrCreate( Application.DisplayAlerts = Fals Dim MyPath As Strin Dim sh As Workshee Workbooks.Open (ThisWorkbook.Path & "\ReportOne.xls" Set sh = ActiveWorkbook.ActiveShee With s .Columns("A:I").AutoFilter Field:=8, Criteria1:= <--Value From Array-- .Columns("A:I").SpecialCells(xlCellTypeVisible).Co p If Dir(ThisWorkbook.Path & "\ProgramData\FileData\StoredData\ <--Value From Array--") < "" The 'Ope Workbooks.Open (ThisWorkbook.Path & "\ProgramData\FileData\StoredData\ <--Value From Array--" & ".xls" Els 'Creat Workbooks.Ad Sheets("Sheet2").Selec ActiveWindow.SelectedSheets.Delet Sheets("Sheet3").Selec ActiveWindow.SelectedSheets.Delet End I Sheets("Sheet1").Range("A1").PasteSpecia .Columns("I:I").AutoFilte .SaveAs Filename:=ThisWorkbook.Path & "\ProgramData\FileData\StoredData\ <--Value From Array--" & ".xls" FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=Fals .Clos End Wit Application.DisplayAlerts = Tru End Su Thanks in advance for your time Jim |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding an Array
If you only have one of 4 possibilities then I would use
a case statement instead of an array. For more information on case statements goto:http://www.mvps.org/dmcritchie/excel/search.htm Dim PERFORMANCE As Integer Select Case PERFORMANCE Case 100 Bonus = salary * 0.1 'if variable PERFORMANCE 100 Case 75 Bonus = salary * 0.075 'if variable PERFORMANCE 75 Case 50 Bonus = salary * 0.05 'if variable PERFORMANCE 50 Case 25 Bonus = salary * 0.025 'if variable PERFORMANCE 25 Case 10 Bonus = salary * 0.01 'if variable PERFORMANCE 10 End Select -----Original Message----- This is similiar to a previous post but I think I have my problem reduced even further. I think all I need here is to set up an array and pass the value of that through my current macro. There are 4 places were the value of the array would need to be inputed (as seen below <-- Value From Array--). The values for the array will be on "sheet2" of the "ReportOne.xls" file, in column "E".There will be anywhere from 1 to 4 values there. The data that the autofilter is filtering will be on "sheet1" of "ReportOne.xls". I need to loop through my current macro passing into it in those four places each value from the array. My problem is I don't really know how to use arrays, especially in this manner - I think it is all I need - and this is the only solution I can put together that can come close to figuring out for my problem. Any help on this would be great. This is as far as I can take this alone. Sub FindOrCreate() Application.DisplayAlerts = False Dim MyPath As String Dim sh As Worksheet Workbooks.Open (ThisWorkbook.Path & "\ReportOne.xls") Set sh = ActiveWorkbook.ActiveSheet With sh .Columns("A:I").AutoFilter Field:=8, Criteria1:= <-- Value From Array-- .Columns("A:I").SpecialCells(xlCellTypeVisible).Co py If Dir(ThisWorkbook.Path & "\ProgramData\FileData\StoredData\ <--Value From Array-- ") < "" Then 'Open Workbooks.Open (ThisWorkbook.Path & "\ProgramData\FileData\StoredData\ _ <--Value From Array--" & ".xls") Else 'Create Workbooks.Add Sheets("Sheet2").Select ActiveWindow.SelectedSheets.Delete Sheets("Sheet3").Select ActiveWindow.SelectedSheets.Delete End If Sheets("Sheet1").Range("A1").PasteSpecial .Columns("I:I").AutoFilter .SaveAs Filename:=ThisWorkbook.Path & "\ProgramData\FileData\StoredData\ _ <--Value From Array--" & ".xls") FileFormat:=xlNormal, _ Password:="", _ WriteResPassword:="", _ ReadOnlyRecommended:=False, _ CreateBackup:=False .Close End With Application.DisplayAlerts = True End Sub Thanks in advance for your time, Jim . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding an Array
Case statements may do the trick, but it will be 1 TO 4 values that my macro will need to cycle throught. And I won't know how many each time, but never less than 1 or more than four. If you still say go with case statements I will look at how to incorporate those, otherwise is it still an array that I need.
Thanks, Jim ----- Marty wrote: ----- If you only have one of 4 possibilities then I would use a case statement instead of an array. For more information on case statements goto:http://www.mvps.org/dmcritchie/excel/search.htm Dim PERFORMANCE As Integer Select Case PERFORMANCE Case 100 Bonus = salary * 0.1 'if variable PERFORMANCE 100 Case 75 Bonus = salary * 0.075 'if variable PERFORMANCE 75 Case 50 Bonus = salary * 0.05 'if variable PERFORMANCE 50 Case 25 Bonus = salary * 0.025 'if variable PERFORMANCE 25 Case 10 Bonus = salary * 0.01 'if variable PERFORMANCE 10 End Select -----Original Message----- This is similiar to a previous post but I think I have my problem reduced even further. I think all I need here is to set up an array and pass the value of that through my current macro. There are 4 places were the value of the array would need to be inputed (as seen below <-- Value From Array--). The values for the array will be on "sheet2" of the "ReportOne.xls" file, in column "E".There will be anywhere from 1 to 4 values there. The data that the autofilter is filtering will be on "sheet1" of "ReportOne.xls". I need to loop through my current macro passing into it in those four places each value from the array. My problem is I don't really know how to use arrays, especially in this manner - I think it is all I need - and this is the only solution I can put together that can come close to figuring out for my problem. Any help on this would be great. This is as far as I can take this alone. Sub FindOrCreate() Application.DisplayAlerts = False Dim MyPath As String Dim sh As Worksheet Workbooks.Open (ThisWorkbook.Path & "\ReportOne.xls") Set sh = ActiveWorkbook.ActiveSheet With sh .Columns("A:I").AutoFilter Field:=8, Criteria1:= <-- Value From Array-- .Columns("A:I").SpecialCells(xlCellTypeVisible).Co py If Dir(ThisWorkbook.Path & "\ProgramData\FileData\StoredData\ <--Value From Array-- ") < "" Then 'Open Workbooks.Open (ThisWorkbook.Path & "\ProgramData\FileData\StoredData\ _ <--Value From Array--" & ".xls") Else 'Create Workbooks.Add Sheets("Sheet2").Select ActiveWindow.SelectedSheets.Delete Sheets("Sheet3").Select ActiveWindow.SelectedSheets.Delete End If Sheets("Sheet1").Range("A1").PasteSpecial .Columns("I:I").AutoFilter .SaveAs Filename:=ThisWorkbook.Path & "\ProgramData\FileData\StoredData\ _ <--Value From Array--" & ".xls") FileFormat:=xlNormal, _ Password:="", _ WriteResPassword:="", _ ReadOnlyRecommended:=False, _ CreateBackup:=False .Close End With Application.DisplayAlerts = True End Sub Thanks in advance for your time, Jim . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding if to Average If Array function | Excel Worksheet Functions | |||
Adding " , ) to text array | Setting up and Configuration of Excel | |||
Adding up with array formula | Excel Worksheet Functions | |||
adding cells within an array | Excel Worksheet Functions | |||
Adding rows to an array | Excel Worksheet Functions |