Code execution interrupted

[DeaPeaJay]

My code is constantly being interrupted for no reason in random spots. It's almost as if I'm constantly pressing ctrl-break (which I'm not) I ran it through once flawlessly. Now, it's another story. I can't make any sense of this... very frustrating.

I could make a microsoft bashing remark right now, but I'm not going to. (thank goodness I have a mac at home)

Also, if the code isn't interrupted my excel.range object doesn't get its assigned range and the macro stops as a result.

Here's my code. Any thoughts would be great! Thank You.

Sub Classifier()
    
    strComplexPath = "C:\Documents and Settings\U771009\Desktop\"
    strFileName = "ComputerClassifications.xls"
    strFullPath = strComplexPath & strFileName
    
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Classification"
    Columns("C:C").ColumnWidth = 16.71
    
    Dim xlRange As Excel.Range
    Dim xlRange2 As Excel.Range
    Dim strToFind As String
    Dim strColumn As String
    Dim strClassification As String
    
    Dim strUnknowns(100) As String
    Dim intUnknCnt As Integer
    
    Dim intCounter As Integer
    Dim intCount2 As Integer
    

    
    'Go through Products
    
    For intCounter = 1 To 10000
        'This is where the range loses its value occasionally.
        Set xlRange = Range("B" & intCounter)
            xlRange.Select
            
        If ActiveCell.FormulaR1C1 = "" Then
            Exit For
        End If
        If (InStr(xlRange.Text, "Bulk") > 0) Then
            strToFind = xlRange.Value
            strToFind = ParseOut(strToFind)
            
            Workbooks("ComputerClassifications.xls").Activate
            strColumn = "A"
            strClassification = ""
            For intCounter2 = 1 To 500
                Set xlRange2 = Range(strColumn & intCounter2)
                If xlRange2.Text = "" Then
                    strColumn = NextColumn(strColumn)
                    intCounter2 = 1
                    If strColumn <> "" Then
                        Set xlRange2 = Range(strColumn & intCounter2)
                    Else
                        Exit For
                    End If
                End If
                If intCounter2 = 1 Then
                    If xlRange2.Text = "" Then
                        Exit For
                    End If
                End If
                If strToFind = xlRange2.FormulaR1C1 Then
                    strClassification = Range(strColumn & 1)
                    Exit For
                End If
            Next
            If strClassification = "" Then
                strUnknowns(intUnknCnt) = strToFind
                intUnknCnt = intUnknCnt + 1
            Else
                Workbooks("OrdStat.xls").Activate
                
                Range("C" & intCounter).FormulaR1C1 = strClassification
            End If
        Else
            'If intCounter <> 1 Then
                'Rows(CStr(intCounter) & ":" & CStr(intCounter)).Select
                'Range("B" & CStr(intCounter)).Activate
                'Selection.Delete Shift:=xlUp
            'End If
        End If
    Next
    
 
    If intUnknCnt > 0 Then
        Dim strMsg As String
        For intCount = 0 To intUnknCnt
            strMsg = strMsg & strUnknowns(intCount) & vbLf
        Next
        MsgBox ("Some Products could not be classified. Please find the appropriate classification." & _
        strMsg)
        
    End If
    
        
End Sub

 

[SkipVought]

Hi,

Looks to me like...

            If strClassification = "" Then
                strUnknowns(intUnknCnt) = strToFind
                intUnknCnt = intUnknCnt + 1
            Else
                Workbooks("OrdStat.xls").Activate
                
                Range("C" & intCounter).FormulaR1C1 = strClassification
            End If

if strClassification = "" then you are STILL in the other workbook when the outer loop cycles again. Tha means that...

    For intCounter = 1 To 10000
        'This is where the range loses its value occasionally.
        [b]Set xlRange = Range("B" & intCounter)[/b]
            xlRange.Select
            
        If ActiveCell.FormulaR1C1 = "" Then
            Exit For
        End If

is referencing that other workbook -- and that is not what you intend.

Skip,

_{[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it!}

 

[DeaPeaJay]

Thank You. Thank You. Thank You. I can't believe I missed that. Apparently, that was the reason for my other problems too.

 

[DeaPeaJay]

Ok, I thought my problem was gone. Basically, I'm running the code. It's making modifications to hundreds and hundreds of cells in a long list via a For..Next loop. When I exit out of the for next loop I move onto another one with some intermediate code in between the two massive loops. THAT's when I get an interruption, around 300 cycles into the next loop it stops. As if I hit ctrl-break. All I have to do is click continue and it keeps running (Unless it decides to break again)

It almost seems as if its running so fast it can't keep up with itself or something. I can't seem to figure out what the problem is. Does anyone have any ideas?

P.S. The code I posted above has had major modifications done to it now, so don't bother looking at it.