VBA For loop

I need a little help getting in the right direction and I should be fine from there.

So the background here is i have a huge 10K line data file I need to search through and find up to three unique results given two conditions. the first condition has a possible of 93 outcomes and the second condition has a possible of 5 outcomes; for a total of about 10k random entries. I want to grow this function to fill lots of data but I keep getting random issues. consistently "error 1004".

This 10k file is updated constantly and i need to create reports for every year in the future so you're help here is really appreciated.

Private Sub CommandButton3_Click()
    Dim LastRow As String
    Dim z As Integer
    Dim CellOne As String
    Dim CellTwo As String
    Dim CellThree As String
    
    'get the row count for the loop
    Sheets("2015 DATA").Select
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row

    'now we loop, match all countries and classes and output the factories
         CellOne = None
         CellTwo = None
         CellThree = None
    
        Sheets("2015 DATA").Select
        Range("A1").Select
        
    For z = 1 To LastRow
    If Worksheets("2015 DATA").Cells(z, 9).Value = "CO" And Worksheets("2015 DATA").Cells(z, 27).Value = "class 12" Then
    
    If CellOne = None Then CellOne = Worksheets("2015 DATA").Cells(z, 7).Value
    If CellTwo = None Then CellOne = Worksheets("2015 DATA").Cells(z, 7).Value
    If CellThree = None Then CellOne = Worksheets("2015 DATA").Cells(z, 7).Value
    
    End If

    Next z
    
      MsgBox (CellOne & CellTwo & CellThree)
End Sub

Why not just use Autofilter?

Filter column I for "CO" and column AA for "class 12"

What you see are just the rows that meet both criteria.

If autofiltering gives the result you want, you can to that with VBA if you wish -- a few thousand times faster than what you have.

Sub IAmG()
    Worksheets("2015 DATA").Select

    With Range("A:AA")
        .Worksheet.AutoFilterMode = False
        .AutoFilter Field:=9, Criteria1:="CO"
        .AutoFilter Field:=27, Criteria1:="class 12"
    End With
End Sub

option basing on your code:

Option Explicit

Sub test()

Dim sh As Worksheet, lrow As Long, j As Long, result
Dim arr27, arr9, arr7, i As Long, mystr As String

If IsError(Evaluate("'2015 DATA'!A1")) Then
    MsgBox "Sheet " & Chr(34) & "2015 DATA" & Chr(34) & " is not found", vbCritical, "Error"
    Exit Sub
Else
    Set sh = Sheets("2015 DATA")
End If

lrow = sh.Cells(Rows.Count, "a").End(xlUp).Row

If lrow = 1 Then
    If sh.Cells(1, 9) = "CO" And sh.Cells(1, 27) = "class 12" Then
        Sheets.Add.Range("a1").Value = sh.Cells(1, 7).Value
        Exit Sub
    End If
End If

arr7 = sh.Range("g1:g" & lrow)
arr9 = sh.Range("i1:i" & lrow)
arr27 = sh.Range("aa1:aa" & lrow)

ReDim result(1 To lrow, 1 To 1)

For i = 1 To lrow
    If arr9(i, 1) = "CO" And arr27(i, 1) = "class 12" Then
        If InStr(mystr, " " & arr7(i, 1) & " ") = 0 Then
            mystr = mystr & " " & arr7(i, 1) & " "
            j = j + 1
            result(j, 1) = arr7(i, 1)
        End If
    End If
Next

Application.ScreenUpdating = 0
Sheets.Add.Range("a1:a" & j) = result
Application.ScreenUpdating = 1

End Sub

Watersev, very nice code. thoughtful error handles.. very nice.
But when I use it I get "runtime error 1004, Application-defined or object-defined error"
It makes a new sheet and throws that error.

If i quote out "Sheets.Add.Range("a1:a" & j) = result" It doesn't error but it still creates a blank sheet.
I added a msgbox at the end and tried to display different things to see if it otherwise worked, it does calculate lrow correctly.. And if i print mystr it gives me every unique value in a string. :thumbsup

I am new to this and this environment, im sure im implementing it wrong or something..

Also I could use a little help understanding whats going on here:

If InStr(mystr, " " & arr7(i, 1) & " ") = 0 Then
mystr = mystr & " " & arr7(i, 1) & " "
j = j + 1
result(j, 1) = arr7(i, 1)

Sub thoughts()
    curMS = inputYear + " MasterSheet"
    curOOT = inputYear + " OOT DATA"
    curMI = inputYear + " MI DATA"
    Dim yval As Integer
    Dim xval As Integer
    Dim classval As Integer
    Dim a As Integer
    Dim sh As Worksheet, lrow As Long, j As Long, result
    Dim arr27, arr9, arr7, i As Long, mystr As String
    Dim crit1 As String, crit2 As String
    Dim q As Long, w As Long, e As Long

'If IsError(Evaluate(curOOT & "!A1")) Then
'this handles errors if the sheet isnt found
 '   MsgBox "Sheet " & Chr(34) & "OOT DATA" & Chr(34) & " is not found", vbCritical, "Error"
 '   Exit Sub
'Else
    Set sh = Sheets(curOOT)
'End If

lrow = sh.Cells(Rows.Count, "a").End(xlUp).Row
'calc the last row in the OOt sheet listed above

If lrow = 1 Then
'error handling for lacking rows
    If sh.Cells(1, 9) = "CO" And sh.Cells(1, 27) = "class 12" Then
        Sheets.Add.Range("a1").Value = sh.Cells(1, 7).Value
        Exit Sub
    End If
End If


ReDim result(1 To lrow, 1 To 1)

    For q = 0 To Sheet1.ListBox2.ListCount - 1
    arr7 = sh.Range("g1:g" & lrow) 'Factory Arr 7 for the 7th row, sets the range from g1 to g-last row
arr9 = sh.Range("i1:i" & lrow) 'country
arr27 = sh.Range("aa1:aa" & lrow) 'class
crit1 = "CO"
crit2 = "Class 11"
        xval = ((q * 3) + 4) 'offset
        'pull country from (xval,1) = country code
    'ReDim result(1 To lrow, 1 To 1)
       
        For w = 1 To 5
                If w = 1 Then
                  yval = 18
                  crit2 = "Class 12"
                  
                  
                          For i = 1 To lrow
            If arr9(i, 1) = crit1 And arr27(i, 1) = crit2 Then
                If InStr(mystr, " " & arr7(i, 1) & " ") = 0 Then
                    mystr = mystr & " " & arr7(i, 1) & " "
                    j = j + 1
                    result(j, 1) = arr7(i, 1)
                End If
            End If
        Next
        If j > 0 Then
            'Application.ScreenUpdating = 0
            With Sheets(curMS)
                    .Range(.Cells(yval, xval), .Cells(yval + 2, xval)) = result
            End With
            'Application.ScreenUpdating = 1
             End If  
                End If
                
                If w = 2 Then
                 yval = 22
                  crit2 = "Class 11"
                  
            For i = 1 To lrow
            If arr9(i, 1) = crit1 And arr27(i, 1) = crit2 Then
                If InStr(mystr, " " & arr7(i, 1) & " ") = 0 Then
                    mystr = mystr & " " & arr7(i, 1) & " "
                    j2 = j2 + 1
                    result(j2, 1) = arr7(i, 1)
                End If
            End If
        Next
        If j > 0 Then
            'Application.ScreenUpdating = 0
            With Sheets(curMS)
                    .Range(.Cells(yval, xval), .Cells(yval + 2, xval)) = result
            End With
            'Application.ScreenUpdating = 1
             End If 
                End If
                
                If w = 3 Then
                    yval = 26
                    classval = 13
                End If
                
                If w = 4 Then
                    yval = 30
                    classval = 9
                End If
                
                If w = 5 Then
                    yval = 36
                    classval = 14
                End If
    'ReDim result(1 To lrow, 1 To 1)

            Next
        Next
    
End Sub

The only way i could get it to work for more than one was how its listed above, i tried to use it as a method, i tried to put it as the third loop in but nothing would work
:/

I guess i should explain it,

Im trying to run three for loops.
1 adjusts for the X values across
2 adjusts for the y values
3 looks up the codes in OOT

What im saying is if i continued the only way i can get it to work, i would need 500 variables and thats hardly a good use of anyone's time lol.

Just downloaded and Im going through it. I feel like I really bit off more than I could chew with this project.. I have a ton of other functionality to complete by the end of work today, if you can help me with this It'll really save me.. If you cant, you have already helped me and I understand..

I really didnt wanna give out a lot of info


So Ill also attach your file with the change I was going after, I need a for loop that grabs either the names out of the first row of cells or pulls them from the listbox and then searches OOT data and fills that.

If theres anything else that would help let me know!

check below

project.xlsm

Here i added a little code to change the countries,

I suggest you move all the items in the listbox from right to left with the "<<" then add a few like china, usa.. countries with lots of data then click build.

Then navigate to module one, i think youll find everything there..
Thanks again!

Just noticed i removed Col A from OOT so youll need to make a row a so lrow works

Works flawlessly

You're the man. If I'm ever in the ukraine, ill buy you a beer.