Subscript out of range error

[makeitwork09]

I am using Excel 2007

I am getting a subscript out of range error at the line that reads as

FoundRange.Offset(0, 6).Value = FieldName(NameCount)

I think the issue starts at the following line, but I do not understand why

FieldName(NameCount) = DiffRange.Offset(4, 0).Value & Chr(10)

Below is all of the code

Sub CreateFormulas()


   Dim LastRow As Long
   Dim LastCol As Long
   Dim lcrow As Long
   Dim lccol As Long
   Dim wsName As Worksheet
   Dim i As Integer
   Dim DiffRange As Range
   Dim FoundRange As Range
   Dim SheetName As String
   Dim FieldName() As String
   Dim NameCount As Integer
   Dim x As Integer


   Application.ScreenUpdating = False


   For i = 3 To Worksheets.Count
       With ThisWorkbook.Worksheets(i)
           .Activate
           SheetName = .Name
           .Range("B3").Value = "diff"
           .Range("B4").Value = "v9"
           .Range("B5").Value = "v11"
           .Range("B6").Value = "subtotal"

           Call GetRowsCols(lcrow, lccol)
           LastRow = lcrow
           LastCol = lccol

           .Range(Cells(4, 3), Cells(4, LastCol)).FormulaR1C1 = "=SUMIF(R8C1:R" & LastRow & _
            "C1,""=v9"",R8C:R" & LastRow & "C)"
           .Range(Cells(5, 3), Cells(5, LastCol)).FormulaR1C1 = "=SUMIF(R8C1:R" & LastRow & _
            "C1,""=v11"",R8C:R" & LastRow & "C)"
           .Range(Cells(6, 3), Cells(6, LastCol)).Formula = "=SUBTOTAL(9,C8:C" & LastRow & ")"
           .Range(Cells(3, 3), Cells(3, LastCol)).Formula = "=C4-C5"
           .Range("A3").FormulaR1C1 = "=IF(NOT(ISERROR(SUM(RC[2]:RC[" & LastCol - 1 & "]))),COUNTIF(RC[2]:RC[" & LastCol - 1 & "],""<>0""),""ERRORS"")"

           'find the columns with differences
           NameCount = 0

           For Each DiffRange In .Range(Cells(3, 3), Cells(3, LastCol))
                If DiffRange.Value <> 0 Then
                    ReDim Preserve FieldName(NameCount)
                    FieldName(NameCount) = DiffRange.Offset(4, 0).Value & Chr(10)
                    NameCount = NameCount + 1
                End If
           Next DiffRange
           
           'update the summary sheet with the names of the fields
           Set FoundRange = ThisWorkbook.Worksheets("AFTER NP").Cells.Find(What:=SheetName, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
           If Not FoundRange Is Nothing Then
            FoundRange.Offset(0, 6).Value = FieldName(NameCount)
           End If
       End With
       
       Erase FieldName() ' deletes the varible contents, free some memory

   Next i

   Application.ScreenUpdating = True


End Sub

 

[Gavona]

Start your detective work by using the watch window to show the value of the expressions:
FoundRange.Offset(0, 6).Value
FoundRange.Offset(0, 6).Address
FieldName(NameCount).value
NameCount

Start by doing this while the code is in break mode showing the error. When you understand what is going wrong then step through your code using a similar approach.



Gavin

 

[PHV]

The issue is that NameCount is greater than UBound(fieldName) ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886