Adding an Array

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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).Cop
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
 
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
Click to expand...
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,
Click to expand...
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).Copy
If Dir(ThisWorkbook.Path
Click to expand...
& "\ProgramData\FileData\StoredData\ <--Value From Array--
") <> "" Then
'Open
Workbooks.Open (ThisWorkbook.Path
Click to expand...
& "\ProgramData\FileData\StoredData\ _
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
Click to expand...
& "\ProgramData\FileData\StoredData\ _
 
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
Click to expand...
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.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.
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:= <--
Click to expand...
Value From Array-->> .Columns("A:I").SpecialCells(xlCellTypeVisible).Copy
If Dir(ThisWorkbook.Path
Click to expand...
& "\ProgramData\FileData\StoredData\ <--Value From Array--
") <> "" Then
'Open
Workbooks.Open (ThisWorkbook.Path
Click to expand...
& "\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
Click to expand...
& "\ProgramData\FileData\StoredData\ _
 
Back
Top