FIND method not working; driving me crazy

[Excel_VBA]

I have 3 sub procedures. automateK(), outline () and checkLabor (). I am beginning with automateK (). Within in automateK(), I am calling outline() then checkLabor().

I am getting through the first iteration of outline(), but I am getting stuck on checkLabor() when I try to use the FIND method.

I am trying to find the first occurence of "TOTAL COSTS." I did check to see if "TOTAL COSTS" appears in Sheets(index).Columns(3) and it does.

The index is being initialized to 4 so that's the 4th sheet from the left. Then within that sheet I want to search in Column C which is the third column. Column A is hidden, but I don't think that should really matter.
And "TOTAL COSTS" does appear under Column C on the 4th sheet so I don't know why this isn't working.

I am thinking it might have something to do with the scope of the object variable used. The problem is on the following line:

Set laborTotal = Sheets(index).Columns(3).Find(what:="TOTAL LABOR")

Any help would greatly be appreciated.

[COLOR=blue]Public lookup As Range[/COLOR]
[COLOR=blue]Public index As Integer[/COLOR]
   
[COLOR=red]Sub automateK()[/COLOR]
    Sheets("Schedule K").Activate
    Sheets("Schedule K").Range("E1").Select
 
    index = 4
 
 
        For Each lookup In Sheets(index).Range("B1:B25350")
            If lookup.Value Like "PRIME CONTRACT I*" Then
                [COLOR=red]Call outline[/COLOR]
[COLOR=red]               Call checkLabor[/COLOR]
            End If
        Next lookup
 
 
End Sub
 
[COLOR=red]Sub outline()[/COLOR]
    Dim division As String
    Dim contractNo As String
    Dim agency As String
    Dim delOrderNo As String
    Dim projectNo As String
    'Dim lookup As Range
 
    division = "XYZ Corporation"
 
    Selection.Value = division
    Selection.Offset(0, 4).Value = "SCHEDULE K"
    Selection.Offset(3, 0).Value = "SUMMARY OF HOURS AND AMOUNTS ON T&M/LABOR HOUR"
    Selection.Offset(4, 0).Value = "CONTRACTS FOR FISCAL YEAR ENDED 12/31/2008"
    Selection.Offset(8, -3).Value = "CONTRACT NO."
    Selection.Offset(9, -3).Value = "AGENCY"
    Selection.Offset(10, -3).Value = "DEL ORDER NO."
    Selection.Offset(11, -3).Value = "LAI PROJECT NO."
 
    Selection.Offset(8, -2).Value = Trim(Right(lookup.Value, Len(lookup.Value) - 18))
    Selection.Offset(9, -2).Value = Trim(Right(lookup.Offset(-1, 0).Value, Len(lookup.Offset(-1, 0).Value) - 7))
    Selection.Offset(10, -2).Value = "'" & Trim(Right(lookup.Offset(1, 0).Value, 3))
    Selection.Offset(11, -2).Value = Left(Trim(Right(lookup.Offset(1, 0).Value, 8)), 4)
 
    'the following lines will create the outline for labor category, billing rates and hours, task #
    Selection.Offset(13, 2).Value = "TASK " & Trim(Right(lookup.Offset(1, 0).Value, 3))
    Selection.Offset(14, 0).Value = "(NOTE 1)"
    Selection.Offset(14, 2).Value = "(NOTE 2)"
    Selection.Offset(15, 0).Value = "RATE"
    Selection.Offset(15, 2).Value = "HOURS"
    Selection.Offset(15, 4).Value = "AMOUNT"
    Selection.Offset(15, -3).Value = "LABOR CATEGORY"
 
    Range(Selection.Offset(13, 0), Selection.Offset(13, 4)).Borders(xlEdgeBottom).LineStyle = xlContinuous
    Range(Selection.Offset(15, -4), Selection.Offset(15, 4)).Borders(xlEdgeBottom).LineStyle = xlContinuous
 
    ' apply formatting to the outline area
    Selection.Offset(0, 4).Font.Bold = True
    Selection.HorizontalAlignment = xlCenter
    Selection.Offset(3, 0).HorizontalAlignment = xlCenter
    Selection.Offset(4, 0).HorizontalAlignment = xlCenter
    Sheets("Schedule K").Columns(3).HorizontalAlignment = xlLeft
    Sheets("Schedule K").Rows(Selection.Offset(13, 0).Row & ":" & Selection.Offset(15, 0).Row).HorizontalAlignment = xlCenter
    Sheets("Schedule K").Columns(2).ColumnWidth = 20    
    Selection.Offset(30, 0).Select
End Sub
 
[COLOR=red]Sub checkLabor()[/COLOR]
 
    Dim laborTotal As Range
 
    Set laborTotal = Sheets(index).Columns(3).Find(what:="TOTAL LABOR")
    Sheets(index).laborTotal.Select
 
End Sub

 

[Gary McMaster]

Just an FYI. Using intrinsic variable names such as "Index" in your code is not a good idea. It may have nothing to do with your problem but IMO you should avoid it.

Gary

 

[Bill Rockenbach]

You are using a reserved word index. I got it to run ok by changing the variable name to index1.

BTW I dimensioned it at the top of the code module with a standard DIM statemnt

Dim index1 as Integer

I don't think you need to make it a Public variable.