Need help with logic for custom filters

[zpele]

I am working on a project for work that parses through an Excel file and pulls out certain things. The Excel file is separated into different studies with about 5-6 rows per study. I want to use the below code to accomplish this but I am pretty positive that the syntax is off. The logic is pretty close I think but I would really appreciate it if some1 could help me rewrite this with correct syntax for VB.

Dim StudyArray() As String
Dim UserNameArray() As String
Dim FailureArray() As String


For StudyArray = LBound(StudyArray) To UBound(StudyArray){
IF (job_status = 'FAILURE'){
ReDim FailureArray(FailureArray + 1)
UBound(FailureArray) = CurrentRow
'The above code will add the current row from StudyArray to the end of the FailureArray
}
For FailureArray = LBound(FailureArray) To UBound(FailureArray){
IF(FailureArray.length > 1){
FOR FailureArray = LBound(FailureArray) to UBound(FailureArray){
UBound(UserNameArray) = 'The part of the array that lists the username in the current entry'
'Populates the UserNameArray to list all of the usernames from the current study from the jobs that have a status of failure
}
IF(UserNameArray.length > 1){
FOR FailureArray = LBound(FailureArray) To UBound(FailureArray){
Sheets("Main").Select
Row(UBound(FailureArray)).Select
Selection.Copy
Sheets("Multiple Users").Select
ActiveSheet.Paste
'Not sure what row this will paste the data to
ReDim FailureArray(FailureArray - 1)
}
}ELSE{
FOR FailureArray =LBound(FailureArray) To UBound(FailureArray){
Sheets("Main").Select
Row(UBound(FailureArray)).Select
Selection.Copy
Sheets("One User").Select
ActiveSheet.Paste
'Not sure what row this will paste the data to
ReDim FailureArray(FailureArray - 1)
}
}
}
}

}

The above code will create 3 arrays. The first array (StudyArray) contains all of the information from the current study being compared. The second array(FailureArray) contains any rows from StudyArray that have a status of ‘Failure’. The third array(UserNameArray) checks FailureArray to see if there are multiple users. If the jobs that have failed have more then one user then the rows will get pasted to the 'Multiple Users' sheet. Otherwise, the rows will get pasted to the 'One User' sheet. My question is whether it is possible to populate the StudyArray (and FailureArray when it copies select rows from StudyArray) with not only the contents of each row in a given study but also the address of the row in Excel. It will make for better dynamic reference when trying to copy and paste.

[inmar32]

Hey
This forum is about visual C++ .
I believe what your looking for is in :
http://www.codeguru.com/forum/forumdisplay.php?f=4

[Eri523]

Yes, inmar32 is right (including the link he posted): VBA is handled in the VB6 forum, so you should better post inquiries about that there.

Also, you should better look up the VBA syntax. It is described in Excels VBA help files. In particular you should look up control structures (If, For), but that's not the only problem in your code.

My question is whether it is possible to populate the StudyArray (and FailureArray when it copies select rows from StudyArray) with not only the contents of each row in a given study but also the address of the row in Excel.

This information is available in the Address property of any Range object (the selection, for instance, is a Range object).

Please use code tags when posting code.

Ah, and... Welcome to CodeGuru!