0

I have a sub in Excel 2010 that's flagging an Application-Defined or Object-Defined Error when I run it. The sub is supposed to populate a sheet with data that's been pulled from Access 2010. This was originally flagging an Overflow error at Row_Rehab = Row_Rehab + 1. I changed Row_Rehab from an Integer to a Long. Now I'm getting this Application-Defined error.

The error occurs at Line 3 (the first If Worksheets....

Do
   Row_Rehab = Row_Rehab + 1
   If Worksheets("Rehab").Cells(Row_Rehab, 1) = Worksheets("Raw Export").Cells(Row_Export, 1) And Worksheets("Rehab").Cells(Row_Rehab, 2) = Worksheets("Raw Export").Cells(Row_Export, 2) Then
      Do
        Row_Rehab = Row_Rehab + 1
          If Worksheets("Rehab").Cells(Row_Rehab, 1) = "" And Worksheets("Rehab").Cells(Row_Rehab, 2) = "" And Worksheets("Rehab").Cells(Row_Rehab, Rehab_GWP_Col) = "" Then
          ProjLine.Copy Destination:=Worksheets("Rehab").Range(Worksheets("Rehab").Cells(Row_Rehab, 1), Worksheets("Rehab").Cells(Row_Rehab, numCol))
          Exit Do
          End If
     Loop
     Exit Do
     End If
Loop

What can I do to fix this error?

EDIT: Here's the full code for this procedure. 

Sub Populate_Rehab()

Dim Row_Export As Long
Dim Row_Rehab As Long
Dim Funding As String
Dim PROJ As String
Dim WorkType As String
Dim Dom As Integer    
Dim Rehab_PROJ_Col As Integer
Dim Export_PROJ_Col As Integer
Dim Rehab_WorkType_Col As Integer
Dim PROJ_Row As Integer    
Dim flag As Boolean    
Dim Export_SF_Col As Integer    
Dim Export_Column As Integer
Dim RawExport_Col(255) As Integer    
Dim numRecords As Long
Dim numCol As Integer
Dim line As Range
Dim PROJLine As Range
Dim WPLine As Range    
Dim numSF As Integer
Dim SF As Integer
Dim col As Integer
Dim startCol, startCol2 As Integer
Dim SFflag As Boolean
Dim SF_Row As Integer
Dim SF_type As String
Dim i, j As Integer 'used in for loop
Dim SFInfo As Range    
Dim numP As Integer
Dim strSearch As String
Dim aCell As Range
Dim startColExp As Integer     
Dim colPriority As Integer
Dim colEngComment As Integer
Dim colSubmissionCRO As Integer
Dim colActualSubmissionCRO As Integer    
Dim colEmpty As Integer
Dim colEmptyS As String
Dim colEmptyE As String    
Dim statusP As Integer    
Dim wks As Integer

RawExport_Col(0) = 0
RawExport_Col(1) = 0
RawExport_Col(2) = 0
RawExport_Col(3) = 0
RawExport_Col(4) = 5
RawExport_Col(5) = 1
RawExport_Col(6) = 2
RawExport_Col(7) = 3
RawExport_Col(8) = 4
RawExport_Col(9) = 6
RawExport_Col(10) = 7
RawExport_Col(11) = 7
RawExport_Col(12) = 8
RawExport_Col(13) = 10
RawExport_Col(14) = 11
RawExport_Col(15) = 12
RawExport_Col(16) = 13
RawExport_Col(17) = 13
RawExport_Col(18) = 17
RawExport_Col(19) = 17
RawExport_Col(20) = 18
RawExport_Col(21) = 19
RawExport_Col(22) = 20
RawExport_Col(23) = 21
RawExport_Col(24) = 22
RawExport_Col(25) = 23
RawExport_Col(26) = 26
RawExport_Col(27) = 27
RawExport_Col(28) = 28
RawExport_Col(29) = 29
RawExport_Col(30) = 30
RawExport_Col(31) = 31
RawExport_Col(32) = 32
RawExport_Col(33) = 33
RawExport_Col(34) = 0
RawExport_Col(35) = 31
RawExport_Col(36) = 34
RawExport_Col(37) = 35
RawExport_Col(38) = 36
RawExport_Col(39) = 37
RawExport_Col(40) = 38
RawExport_Col(41) = 39
RawExport_Col(42) = 40
RawExport_Col(43) = 14
RawExport_Col(44) = 0
RawExport_Col(45) = 0
RawExport_Col(46) = 0
RawExport_Col(47) = 0
RawExport_Col(48) = 0
RawExport_Col(49) = 0
RawExport_Col(50) = 0
RawExport_Col(51) = 0
RawExport_Col(52) = 0
RawExport_Col(53) = 0
RawExport_Col(54) = 0
RawExport_Col(55) = 0
RawExport_Col(56) = 0

colPriority = 9
colEngComment = 15
colSubmissionCRO = 24
colActualSubmissionCRO = 25

Export_SF_Col = 34

Rehab_PROJ_Col = 7
Export_PROJ_Col = 10
Rehab_WorkType_Col = 22

'search the first added extra column

strSearch = "SOPri"
Set aCell = Worksheets("Raw Export").Rows(1).Find(What:=strSearch, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
If Not aCell Is Nothing Then
    numP = aCell.Column
End If          

'Get the funding types/total number of funding types
With Worksheets("Template")
    numSF = .Range(.Cells(12, 1), .Cells(12, 1).End(xlToRight)).Count - 2
End With

'Count number of records from dataset
With Worksheets("Raw Export")
    numRecords = .Range(.Cells(2, 3), .Cells(2, 3).End(xlDown)).Count
End With

'define expansion column
startColExp = numP + 17
RawExport_Col(startColExp) = numSF + 41
RawExport_Col(startColExp + 1) = numSF + 42
RawExport_Col(startColExp + 2) = numSF + 43
RawExport_Col(startColExp + 3) = numSF + 44
RawExport_Col(startColExp + 4) = numSF + 45
RawExport_Col(startColExp + 5) = numSF + 46
RawExport_Col(startColExp + 6) = numSF + 47
RawExport_Col(startColExp + 7) = numSF + 48

'add allocation fomula if its expansion
If Worksheets("Parameters").Cells(18, 14) = "Expansion" Then
    With Worksheets(2)
        For j = 0 To 7
            .Cells(globalAllocation, 41 + numSF + j).FormulaR1C1 = "=SUM(R[-" + CStr(globalAllocation - 3) + "]C:R[-1]C)"
            .Cells(globalAllocation + 2, 41 + numSF + j).FormulaR1C1 = "=SUM(R[3]C:R[65523]C)"
            .Cells(globalAllocation + 3, 41 + numSF + j).FormulaR1C1 = "=R[-3]C-R[-1]C"
            .Range(.Cells(globalAllocation, 41 + numSF), .Cells(globalAllocation + 3, 41 + numSF + 7)).NumberFormat = "$#,##0"
            Worksheets("Template").Range("BD2:BK2").Copy Destination:=.Range(.Cells(2, 41 + numSF), .Cells(2, 41 + numSF + 7))
            .Range(.Cells(2, 41 + numSF), .Cells(2, 41 + numSF + 7)).ColumnWidth = 13.71
            .Range(.Cells(2, 41 + numSF), .Cells(2, 41 + numSF + 7)).NumberFormat = "$#,##0"
        Next
    End With
    For wks = 3 To (Worksheets.Count) - 2
        With Worksheets(wks)
            For j = 0 To 7
                .Cells(5, 41 + numSF + j).FormulaR1C1 = "=SUM(R[3]C:R[65531]C)"
                .Cells(6, 41 + numSF + j).FormulaR1C1 = "=R[-3]C-R[-1]C"
                Worksheets("Template").Range("BD2:BK2").Copy Destination:=.Range(.Cells(2, 41 + numSF), .Cells(2, 41 + numSF + 7))
                .Range(.Cells(2, 41 + numSF), .Cells(2, 41 + numSF + 7)).ColumnWidth = 13.71
                .Range(.Cells(5, 41 + numSF), .Cells(6, 41 + numSF + 7)).NumberFormat = "$#,##0"
            Next
        End With
    Next
End If

'This will count the number of columns to include as per the header
With Worksheets("Template")
    numCol = .Range(.Cells(2, 1), .Cells(2, RawExport_Col(36)).End(xlToRight)).Count

    'Set the PROJ and WP template line
    Set PROJLine = .Range(.Cells(6, 1), .Cells(6, numCol))
    Set WPLine = .Range(.Cells(8, 1), .Cells(8, numCol))
End With

'----------------------------------START POPULATE THE SHEETS    
Row_Export = 1
Do
    Row_Export = Row_Export + 1
    SFflag = False 'set flag false since new PROJ
    'Set status bar
    statusP = CInt((Row_Export - 1) / (numRecords + 1) * 100)
    If statusP = 10 Or statusP = 30 Or statusP = 60 Or statusP = 90 Then
        Application.Wait (Now() + 0.00001)
    End If

    Application.StatusBar = "Please wait while performing task 7 (" + CStr(statusP) + "%) of 7 ..."

    If Worksheets("Raw Export").Cells(Row_Export, 3) = "" Then 'Funding Area
        Exit Do
    ElseIf Not (Worksheets("Raw Export").Cells(Row_Export, 1) = "" Or Worksheets("Raw Export").Cells(Row_Export, 2) = "") Then
        With Worksheets("Rehab")
            Row_Rehab = .Range(.Cells(2, RawExport_Col(32)), .Cells(2, RawExport_Col(32)).End(xlDown)).Count + 2
        End With
        Do
            Row_Rehab = Row_Rehab + 1
            If Worksheets("Rehab").Cells(Row_Rehab, 1) = Worksheets("Raw Export").Cells(Row_Export, 1) And Worksheets("Rehab").Cells(Row_Rehab, 2) = Worksheets("Raw Export").Cells(Row_Export, 2) Then
                Do
                    Row_Rehab = Row_Rehab + 1
                    If Worksheets("Rehab").Cells(Row_Rehab, 1) = "" And Worksheets("Rehab").Cells(Row_Rehab, 2) = "" And Worksheets("Rehab").Cells(Row_Rehab, Rehab_PROJ_Col) = "" Then
                        PROJLine.Copy Destination:=Worksheets("Rehab").Range(Worksheets("Rehab").Cells(Row_Rehab, 1), Worksheets("Rehab").Cells(Row_Rehab, numCol))
                        Exit Do
                    End If
                Loop
                Exit Do
            End If
        Loop

        PROJ = Worksheets("Raw Export").Cells(Row_Export, Export_PROJ_Col)
        '-----------------------------PROJ LINE
        For Export_Column = 4 To 10
            Worksheets("Rehab").Cells(Row_Rehab, RawExport_Col(Export_Column)) = Worksheets("Raw Export").Cells(Row_Export, Export_Column)
        Next
        For Export_Column = 13 To 16
            Worksheets("Rehab").Cells(Row_Rehab, RawExport_Col(Export_Column)) = Worksheets("Raw Export").Cells(Row_Export, Export_Column)
        Next
        If Worksheets("Raw Export").Cells(Row_Export, 18) = "" Then
            Worksheets("Rehab").Cells(Row_Rehab, RawExport_Col(18)) = Worksheets("Raw Export").Cells(Row_Export, 19)
        ElseIf Worksheets("Raw Export").Cells(Row_Export, 19) = "" Then
            Worksheets("Rehab").Cells(Row_Rehab, RawExport_Col(18)) = Worksheets("Raw Export").Cells(Row_Export, 18)
        End If
        For Export_Column = 20 To 33
            If Export_Column = 25 Then
                If Worksheets("Raw Export").Cells(Row_Export, Export_Column) = True Then
                    Worksheets("Rehab").Cells(Row_Rehab, RawExport_Col(Export_Column)) = "YES"
                End If
            ElseIf Export_Column = 27 Or Export_Column = 29 Then
                If Worksheets("Raw Export").Cells(Row_Export, Export_Column) = True Then
                    Worksheets("Rehab").Cells(Row_Rehab, RawExport_Col(Export_Column)).Font.Name = "Wingdings"
                    Worksheets("Rehab").Cells(Row_Rehab, RawExport_Col(Export_Column)) = Chr(252)
                End If
            Else
                Worksheets("Rehab").Cells(Row_Rehab, RawExport_Col(Export_Column)) = Worksheets("Raw Export").Cells(Row_Export, Export_Column)
            End If
        Next
        For Export_Column = 36 To 42
            If Not Worksheets("Raw Export").Cells(Row_Export, Export_Column) = 0 Then
                Worksheets("Rehab").Cells(Row_Rehab, RawExport_Col(Export_Column)) = Worksheets("Raw Export").Cells(Row_Export, Export_Column)
            End If
        Next                    

        Worksheets("Rehab").Cells(Row_Rehab, 14) = Worksheets("Raw Export").Cells(Row_Export, 43)
        Worksheets("Rehab").Cells(Row_Rehab, 16) = Worksheets("Raw Export").Cells(Row_Export, numP + 10)
        Worksheets("Rehab").Cells(Row_Rehab, colEngComment) = Worksheets("raw export").Cells(Row_Export, numP + 3)
        Worksheets("rehab").Cells(Row_Rehab, colSubmissionCRO) = Worksheets("raw export").Cells(Row_Export, numP + 4)
        Worksheets("Rehab").Cells(Row_Rehab, colPriority) = Worksheets("raw export").Cells(Row_Export, numP + 11)


        If Worksheets("raw export").Cells(Row_Export, numP + 5) = False Then
            Worksheets("rehab").Cells(Row_Rehab, colActualSubmissionCRO) = ""
        Else
            Worksheets("rehab").Cells(Row_Rehab, colActualSubmissionCRO) = Chr(252)
            Worksheets("Rehab").Cells(Row_Rehab, colActualSubmissionCRO).Font.Name = "Wingdings"
        End If
                '----------Expansion only
        If Worksheets("Parameters").Cells(18, 14) = "Expansion" Then
            For Export_Column = startColExp To (startColExp + 7)
                If Not Worksheets("raw export").Cells(Row_Export, Export_Column) = 0 Then
                    Worksheets("Rehab").Cells(Row_Rehab, RawExport_Col(Export_Column)) = Worksheets("Raw Export").Cells(Row_Export, Export_Column)
                    Worksheets("Rehab").Cells(Row_Rehab, RawExport_Col(Export_Column)).NumberFormat = "$#,##0"
                End If
            Next
        End If
        '-----------------------------------
                   PROJ_Row = Row_Rehab
        Worksheets("Rehab").Rows(Row_Rehab).RowHeight = 12.75
        Row_Rehab = Row_Rehab + 1
        Worksheets("Rehab").Rows(Row_Rehab).EntireRow.Insert
        Row_Rehab = Row_Rehab - 1
        '-------------------SUBWB LINES
        Do
            If Not Worksheets("Raw Export").Cells(Row_Export, Export_PROJ_Col) = PROJ Then
                Row_Export = Row_Export - 1
                Exit Do
            Else
                Row_Rehab = Row_Rehab + 1
                Worksheets("Rehab").Rows(Row_Rehab).EntireRow.Insert
                WPLine.Copy Destination:=Worksheets("Rehab").Range(Worksheets("Rehab").Cells(Row_Rehab, 1), Worksheets("Rehab").Cells(Row_Rehab, numCol))

                Worksheets("Rehab").Cells(Row_Rehab, RawExport_Col(11)) = Worksheets("Raw Export").Cells(Row_Export, 11)
                If Worksheets("Raw Export").Cells(Row_Export, 12) = True Then
                    Worksheets("Rehab").Cells(Row_Rehab, RawExport_Col(12)) = "DOM"
                End If

                Worksheets("Rehab").Cells(Row_Rehab, RawExport_Col(15)) = Worksheets("Raw Export").Cells(Row_Export, 15)
                Worksheets("Rehab").Cells(Row_Rehab, RawExport_Col(17)) = Worksheets("Raw Export").Cells(Row_Export, 17)

                If Worksheets("Raw Export").Cells(Row_Export, 18) = "" Then
                    Worksheets("Rehab").Cells(Row_Rehab, RawExport_Col(18)) = Worksheets("Raw Export").Cells(Row_Export, 19)
                ElseIf Worksheets("Raw Export").Cells(Row_Export, 19) = "" Then
                    Worksheets("Rehab").Cells(Row_Rehab, RawExport_Col(18)) = Worksheets("Raw Export").Cells(Row_Export, 18)
                End If

                For Export_Column = 20 To 24
                    Worksheets("Rehab").Cells(Row_Rehab, RawExport_Col(Export_Column)) = Worksheets("Raw Export").Cells(Row_Export, Export_Column)
                Next

                Worksheets("Rehab").Cells(Row_Rehab, RawExport_Col(35)) = Worksheets("Raw Export").Cells(Row_Export, 35)

                WorkType = Worksheets("Rehab").Cells(Row_Rehab, Rehab_WorkType_Col)
                Set line = Worksheets("Rehab").Range(Worksheets("Rehab").Cells(Row_Rehab, 1), Worksheets("Rehab").Cells(Row_Rehab, numCol))
                If Not (InStr(UCase(WorkType), "STR") > 0) And Not (InStr(UCase(WorkType), "SAF") > 0) Then
                    line.Interior.ColorIndex = 36 'yellow
                    Worksheets("Rehab").Cells(Row_Rehab, colPriority) = Worksheets("raw export").Cells(Row_Export, numP + 2)
                ElseIf InStr(UCase(WorkType), "STR") > 0 Then
                    line.Interior.ColorIndex = 34 'blue
                    Worksheets("Rehab").Cells(Row_Rehab, colPriority) = Worksheets("raw export").Cells(Row_Export, numP + 1)
                ElseIf InStr(UCase(WorkType), "SAF") > 0 Then
                    line.Interior.ColorIndex = 38 'pink
                    Worksheets("Rehab").Cells(Row_Rehab, colPriority) = Worksheets("raw export").Cells(Row_Export, numP)
                End If

                'Check if special funding applies to this WP
                'Check each special funding type
                For SF = 1 To numSF 'for each special funding type - will not execute if none
                    startCol = Worksheets("Template").Cells(11, SF + 2)
                        'Designer
                        If Worksheets("Raw Export").Cells(Row_Export, numP + 6) = Worksheets("Template").Cells(12, SF + 2) Then
                            Worksheets("Rehab").Cells(PROJ_Row, RawExport_Col(42) + SF) = "YES"
                            If Not Worksheets("raw export").Cells(Row_Export, numP + 6) = "" Then
                                If Worksheets("Raw export").Cells(Row_Export, numP + 6) = "In House" Or Worksheets("raw export").Cells(Row_Export, numP + 10) = "" Then
                                    Worksheets("Rehab").Cells(Row_Rehab, RawExport_Col(42) + SF).Font.Name = "Wingdings"
                                    Worksheets("Rehab").Cells(Row_Rehab, RawExport_Col(42) + SF) = Chr(252)
                                Else
                                    Worksheets("rehab").Cells(Row_Rehab, RawExport_Col(42) + SF) = Worksheets("raw export").Cells(Row_Export, numP + 10)
                                End If
                            End If
                        End If

                        'Delivery Method
                        If Worksheets("Raw Export").Cells(Row_Export, numP + 9) = Worksheets("Template").Cells(12, SF + 2) Then
                            Worksheets("Rehab").Cells(PROJ_Row, RawExport_Col(42) + SF) = "YES"
                            If Not Worksheets("raw export").Cells(Row_Export, numP + 9) = "" Then
                                Worksheets("rehab").Cells(Row_Rehab, RawExport_Col(42) + SF) = Worksheets("raw export").Cells(Row_Export, numP + 8)
                                Worksheets("Rehab").Cells(Row_Rehab, RawExport_Col(42) + SF).WrapText = False
                            End If
                        End If

                        'SFunding
                        If Worksheets("raw export").Cells(Row_Export, Export_SF_Col) <> "MNDM Design Build" _
                            And Worksheets("raw export").Cells(Row_Export, Export_SF_Col) <> "MTO Design Build" _
                            And Worksheets("raw export").Cells(Row_Export, Export_SF_Col) <> "In House" Then
                            If Worksheets("Raw Export").Cells(Row_Export, Export_SF_Col) = Worksheets("Template").Cells(12, SF + 2) Then
                                Worksheets("Rehab").Cells(PROJ_Row, RawExport_Col(42) + SF) = "YES"
                                If Not Worksheets("raw export").Cells(Row_Export, 34) = "" Then
                                    Worksheets("Rehab").Cells(Row_Rehab, RawExport_Col(42) + SF).Font.Name = "Wingdings"
                                    Worksheets("Rehab").Cells(Row_Rehab, RawExport_Col(42) + SF) = Chr(252)
                                End If
                            End If
                        End If

                        If Worksheets("Rehab").Cells(PROJ_Row, RawExport_Col(42) + SF) = "YES" Then
                            Worksheets("Rehab").Cells(PROJ_Row, RawExport_Col(42) + SF).Interior.ColorIndex = Worksheets("Rehab").Cells(2, RawExport_Col(42) + SF).Interior.ColorIndex
                            SFflag = True
                        End If
                Next SF
                Worksheets("Rehab").Rows(Row_Rehab).RowHeight = 12.75
                Row_Export = Row_Export + 1
            End If
        Loop
        '------------------------End SUBWP Line

        '------------------------Special Funding Tabs
        'Add to special funding tab iF special funding applied
        If SFflag Then
            For SF = 1 To numSF 'check if special funding type applies
                If Worksheets("Rehab").Cells(PROJ_Row, RawExport_Col(42) + SF) = "YES" Then
                'find place for that project in the tab
                    SF_type = Worksheets("Rehab").Cells(2, RawExport_Col(42) + SF)
                    With Worksheets(SF_type)
                        SF_Row = .Range(.Cells(2, RawExport_Col(32)), .Cells(2, RawExport_Col(32)).End(xlDown)).Count + 2
                    End With
                    Do 'Find the header
                        SF_Row = SF_Row + 1
                        If Worksheets(SF_type).Cells(SF_Row, 1) = Worksheets("Raw Export").Cells(Row_Export, 1) And Worksheets(SF_type).Cells(SF_Row, 2) = Worksheets("Raw Export").Cells(Row_Export, 2) Then
                            'Found header, now find next available spot
                            Do
                                SF_Row = SF_Row + 1
                                'Check if next line is free by checking if PROJ null
                                If (Worksheets(SF_type).Cells(SF_Row, 7) = "") Then
                                'Found free row, add project above it
                                'Add new lines
                                    For i = 0 To (Row_Rehab - PROJ_Row)
                                        Worksheets(SF_type).Rows(SF_Row).EntireRow.Insert
                                    Next i
                                    'Select added new lines
                                    With Worksheets(SF_type)
                                        Set SFInfo = .Range(.Cells(SF_Row, 1), .Cells(SF_Row + (Row_Rehab - PROJ_Row), numCol))
                                    End With
                                    'Copy rehab project to the range
                                    With Worksheets("Rehab")
                                        .Range(.Cells(PROJ_Row, 1), .Cells(Row_Rehab, numCol)).Copy Destination:=SFInfo
                                    End With
                                    'Copy costs into range
                                    startCol = Worksheets("Template").Cells(11, SF + 2)
                                    startCol2 = Worksheets("Template").Cells(9, SF + 2)
                                    For col = 0 To 6
                                        Worksheets(SF_type).Cells(SF_Row, RawExport_Col(36) + col) = Worksheets("Raw Export").Cells(Row_Export, startCol + col)
                                    Next col
                                    'Copy costs for extra years if its expansion
                                    If Worksheets("Parameters").Cells(18, 14) = "Expansion" Then
                                        For col = 0 To 7
                                            Worksheets(SF_type).Cells(SF_Row, 41 + numSF + col) = Worksheets("Raw Export").Cells(Row_Export, startCol2 + col)
                                        Next col
                                    End If
                                    Set SFInfo = Nothing
                                    Exit Do
                                End If
                            Loop
                            Exit Do
                        End If
                    Loop
                End If
            Next SF
        End If
        '------------------------End Special Funding Tabs
    End If
Loop

Application.StatusBar = "Formatting..."

For wks = 2 To (Worksheets.Count - 2)
    With Worksheets(wks)
        .Columns("A:C").ColumnWidth = 7.57 'Year
        .Columns("D:D").ColumnWidth = 9.86 'Contract
        .Columns("F:F").ColumnWidth = 4 'Area
        .Columns("G:G").ColumnWidth = 14.57 'PROJ
        .Columns("H:H").ColumnWidth = 3.43 'DOM
        .Columns("I:I").ColumnWidth = 6 'Priority
        .Columns("J:J").ColumnWidth = 9.57 'Contingency Year
        .Columns("L:L").ColumnWidth = 6 'Hwy
        .Columns("M:M").ColumnWidth = 31.71 'Location
        .Columns("N:O").ColumnWidth = 16.14 'Comment
        .Columns("V:V").ColumnWidth = 23 'Project Type
        .Columns("R:U").ColumnWidth = 6.43 'LHRS
        .Columns("X:AB").ColumnWidth = 10.29 'Date
        .Columns("Y:Y").ColumnWidth = 2.57 'Actual
        .Columns("AA:AA").ColumnWidth = 2.57 'Actual
        .Columns("AC:AC").ColumnWidth = 2.57 'Actual
        .Columns("AE:AG").ColumnWidth = 12.5
        .Columns("AH:AN").ColumnWidth = 13.71 'Cost
        .Rows("2:2").EntireRow.AutoFit
    End With
Next

'Finalize - Rearrange the column, format color

'last column before defined print area end
If Worksheets("Parameters").Cells(18, 14) = "Expansion" Then
    colEmptyE = Cells(1, 40 + numSF).Address(1, 0)
    colEmptyE = Left(colEmptyE, InStr(colEmptyE, "$") - 1)

    For wks = 2 To (Worksheets.Count) - 2
            With Worksheets(wks)
                .PageSetup.PrintArea = "A:" & colEmptyE & ""
            End With
    Next wks
End If

Application.CutCopyMode = False
Worksheets(2).Name = Worksheets("Parameters").Cells(18, 14).Value

ActiveWorkbook.Save

End Sub
Improve this question
15
  • What is Row_Export and it's value? If it is 0 at any point you will get an application error. Commented Jul 3, 2015 at 13:19
  • From a little earlier in the code: Row_Export = 1; Do; Row_Export = Row_Export + 1 Commented Jul 3, 2015 at 13:21
  • and is Row_Export dimensioned in the same procedure? i.e. is it still in scope at this point? Commented Jul 3, 2015 at 13:22
  • Yes, it's all in the same procedure. Commented Jul 3, 2015 at 13:23
  • What happens if you declare and look up your sheets before your do loop: Dim ShtRehab As Worksheet and Set ShtRehab = Worksheets("Rehab") and the same for the export? (It is good practice anyway, to do this only once, instead of looling up your sheets time and again.) Commented Jul 3, 2015 at 13:32

3 Answers 3

Reset to default
1

Change the first Do to:

Do While Row_Export <= Sheets("Raw Export").Rows.Count
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

It is also overkill, as you still process the whole sheet.
The OP hasn't stated how many rows they are iterating over - so how do we know it's not the whole sheet?
@MechanicalNotaprogrammer is the error occuring when the code first runs, or does it happen after a while?
It happens after a while. This error is about half-way through the procedure, and it's the last procedure to run.
It happens after a while. This error is about half-way through the procedure, and it's the last procedure to run. Also, Raw Export has 254 rows.
|
0

Try this. If line 'Debug.Print Row_Export' is zero or negative the code is not right.

debug.print can be used to checked values assigned to our variables. In this case the number should be positive and greater than zero.

Do
   Row_Rehab = Row_Rehab + 1
   Debug.Print Row_Rehab
   Debug.Print Row_Export
   If Worksheets("Rehab").Cells(Row_Rehab, 1) = Worksheets("Raw Export").Cells(Row_Export, 1) And Worksheets("Rehab").Cells(Row_Rehab, 2) = Worksheets("Raw Export").Cells(Row_Export, 2) Then
      Do
        Row_Rehab = Row_Rehab + 1
          If Worksheets("Rehab").Cells(Row_Rehab, 1) = "" And Worksheets("Rehab").Cells(Row_Rehab, 2) = "" And Worksheets("Rehab").Cells(Row_Rehab, Rehab_GWP_Col) = "" Then
          ProjLine.Copy Destination:=Worksheets("Rehab").Range(Worksheets("Rehab").Cells(Row_Rehab, 1), Worksheets("Rehab").Cells(Row_Rehab, numCol))
          Exit Do
          End If
     Loop
     Exit Do
     End If
Loop
Improve this answer

5 Comments

The numbers are both above zero; 1048577 and 20 respectively.
You passed the bottom of your sheet. Try [ctrl]-G, fill in A1048576 and press enter, then try the same thing with A1048577
There are only 1,048,576 rows in Excel - that's why you're getting an error.
Instead of do . . . loop, try For Row_Export = 1 To ShtRehab.UsedRange.Rows.Count . . . Next
Row_Export is taking up wrong value unless your entire sheet is filled up with data.
0

Not shure if I understand your objectives, but I would go for something like this:

Set ShtRehab = Worksheets("Rehab")
Set ShtExport = Worksheets("Raw Export")
matchFound = False
For Each RowRehab In ShtRehab.UsedRange.Rows
    ' Find the keys that match those in your export sheet
    If RowRehab.Cells(1, 1) = ShtExport.Cells(Row_Export, 1) _
    And RowRehab.Cells(1, 2) = ShtExport.Cells(Row_Export, 2) Then
        matchFound = True
    Else
        ' Now copy to the first key-less line after it
        If RowRehab.Cells(1, 1) = "" _
        And RowRehab.Cells(1, 2) = "" _
        And RowRehab.Cells(1, Rehab_GWP_Col) = "" Then
            ProjLine.Copy Destination:=RowRehab
            Exit For
        End If
    End If
Next
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.