VBA: Searching for matches in array excel not responding

[knacksc2]

I am still learning how to use arrays well, but this appears to be working until it starts the br loop. the computer fan starts running high and when i click on excel it says not responding. what am i doing wrong?

Public Sub ReadToFromArray()

ActiveWorkbook.Worksheets("Data").Select
Dim a As Long
a = Application.WorksheetFunction.CountA(ActiveWorkbook.Worksheets("Data").Range(Cells(2, 1), Cells(1048576, 1)))


PRnum = Array(1, 5, 10, 11, 13, 18, 20, 21, 22, 23, 25, 28, 29, 33, 35)
PRtext = Array("1", "5", "10", "11", "13", "18", "20", "21", "22", "23", "25", "28", "29", "33", "35")


ActiveWorkbook.Worksheets("Arrays").Select
' Declare dynamic array for Pri to Br
Dim Network As Variant
Network = ActiveWorkbook.Worksheets("Arrays").Range(Cells(4, 2), Cells(36, 16)).Value


'delete the data we will not be using "B4:P36"
'ActiveWorkbook.Worksheets("Data").Columns("AG:AY").EntireColumn.Delete
'ActiveWorkbook.Worksheets("Data").Columns("O:AE").EntireColumn.Delete
'ActiveWorkbook.Worksheets("Data").Columns("K:L").EntireColumn.Delete
'ActiveWorkbook.Worksheets("Data").Columns("H:I").EntireColumn.Delete
'ActiveWorkbook.Worksheets("Data").Columns("C:F").EntireColumn.Delete


ActiveWorkbook.Worksheets("Data").Select
' Declare dynamic array for entire data set
Dim Dataset As Variant
Dataset = ActiveWorkbook.Worksheets("Data").Range(Cells(2, 1), Cells(a + 1, 7)).Value


For p = 0 To UBound(PRnum)


ActiveWorkbook.Worksheets("BR" & PRnum(p)).Select


'clean and populate primary data
ActiveWorkbook.Worksheets("BR" & PRnum(p)).Rows("5:1048576").EntireRow.Delete
Dim x As Long
x = 0
For li = 1 To a
If Dataset(li, 1) = PRnum(p) And Dataset(li, 3) = PRnum(p) And Dataset(li, 7) > 0 Then
ActiveWorkbook.Worksheets("BR" & PRnum(p)).Cells(x + 5, 2).Value = Dataset(li, 2)
ActiveWorkbook.Worksheets("BR" & PRnum(p)).Cells(x + 5, 3).Value = Dataset(li, 4)
ActiveWorkbook.Worksheets("BR" & PRnum(p)).Cells(x + 5, 4).Value = Round(Dataset(li, 7), 0)
If ActiveWorkbook.Worksheets("BR" & PRnum(p)).Cells(x + 5, 4).Value <= ActiveWorkbook.Worksheets("BR" & PRnum(p)).Cells(x + 5, 3).Value Then
Else
ActiveWorkbook.Worksheets("BR" & PRnum(p)).Cells(x + 5, 5).Value = ActiveWorkbook.Worksheets("BR" & PRnum(p)).Cells(x + 5, 4).Value - ActiveWorkbook.Worksheets("BR" & PRnum(p)).Cells(x + 5, 3).Value
End If
x = (x + 1)
Else
End If
Next li


Dim y As Long
y = Application.WorksheetFunction.CountA(ActiveWorkbook.Worksheets("BR" & PRnum(p)).Range(Cells(5, 2), Cells(1048576, 2)))


Dim Lines As Variant
Lines = ActiveWorkbook.Worksheets("BR" & PRnum(p)).Range(Cells(5, 2), Cells(y + 4, 2)).Value


For br = 1 To 33


    For pli = 1 To y
    
        For li2 = 1 To a
        
        If Dataset(li2, 2) = Lines(pli, 1) And Dataset(li2, 1) = Network(p + 1, br) And Dataset(li2, 7) > 0 Then
        
        ActiveWorkbook.Worksheets("BR" & PRnum(p)).Cells(pli + 4, (br * 2) + 12).Value = Dataset(li2, 4)
        ActiveWorkbook.Worksheets("BR" & PRnum(p)).Cells(pli + 4, (br * 2) + 13).Value = Round(Dataset(li2, 7), 0)


        GoTo FoundIt
        Else
        
        End If


        Next li2
FoundIt:
    Next pli


Next br
Next p
End Sub

 

[hotabae]

Use the breakpoint thing again, and when you get to the MsgBox line, don't continue stepping through. Hover your mouse cursor over "dataset(li2,2)" and the rest of those arrays. Just take a look at what values it is returning. They should pop up when you hover over them.

You can usually hover over variables in break mode and see their current value. Check and make sure all the variables look like they are supposed to. Problems would include Null values, ="" looking things, or just stuff you aren't really expecting to see.

 

[knacksc2]

very cool learning tool to hover like that. i did so and when i hover over this

TypeName(Lines(pli, 1))

it returns type mismatch<typemismatch></typemismatch>

 

[hotabae]

i divided the if statement out to see if i could gain better insight, and it failed on the second iteration of the 'li2 loop at the first if line shown below:

For br = 1 To 33    For pli = 1 To 10 'y
    
        For li2 = 1 To 10 'a
        
        'If Lines(pli, 1) = Dataset(li2, 2) And Dataset(li2, 1) = Network(br, p + 1) And Dataset(li2, 7) > 0 Then
        If Lines(pli, 1) = Dataset(li2, 2) Then
        If Dataset(li2, 1) = Network(br, p + 1) Then
        If Dataset(li2, 7) > 0 Then

You mentioned that It failed on the second iteration. That leads me to believe that Lines() and Dataset() are working as intended because it must've been okay when it ran that 'If' statement the first time.
There could be any number of things wrong, and it's hard for me to tell without debugging it myself (symptom of being a noobie). But normally, I would just simplify the code to find the part that isn't working.

For example: MsgBox(Network(br,p+1)) or those typeof() checks from before. Try doing them one at a time or commenting out the first 'If' statement. Stuff like that can help isolate the problem. That step-Through function is your friend.

 

[knacksc2]

on it, thanks so much for the guidance thus far. i will be posting findings as i come across something that looks like it can be solved.

is it ok that variant 'Lines' is defined within the first loop?

 

[knacksc2]

so it turns out, i misunderstood how the step through tool works. it did not fail on the second iteration.. it failed on the first. i tried pulling the data directly from the sheet like this to replace the error and it gives me the same problem. here is the code:

For br = 1 To 33    
For pli = 1 To y
    
        For li2 = 1 To a
        'MsgBox "DatasetType = " & TypeName(Dataset(li2, 2)) & vbNewLine & "LinesType = " & TypeName(Lines(pli, 1)) & vbNewLine & "NetworkType = " & TypeName(Network(br, p + 1))
        'If CLng(Lines(pli, 1)) = CLng(Dataset(li2, 2)) And CLng(Dataset(li2, 1)) = CLng(Network(br, p + 1)) And CLng(Dataset(li2, 7)) > 0 Then
        If CLng(ActiveWorkbook.Worksheets("BR" & PRnum(p)).Cells(pli + 4, 2)) = CLng(Dataset(li2, 2)) Then
        If Dataset(li2, 1) = Network(br, p + 1) Then
        If Dataset(li2, 7) > 0 Then

 

[hotabae]

on it, thanks so much for the guidance thus far. i will be posting findings as i come across something that looks like it can be solved.

is it ok that variant 'Lines' is defined within the first loop?

I mean, as a quick check for that you could move this line -- Dim Lines As Variant -- Up out of the loop.


Sorry I can't be more helpful, I've gotten more info through trial and error than actual instruction. Debugging is a big part of any coding, so it's good to learn those tricks. Step through the code and make sure everything is behaving like it should. Doing MsgBox checks can help a lot too; You could Msgbox(Lines(1,1)) and see what happens (that would take the loops outta the equation).