Expert: Tom Ogilvy - 11/5/2009

Question
Hey Tom,

I've been working on another project that takes a number of columns from one sheet, looks for certain characteristics in the data to populate new arrays which I then write to a different sheet in the workbook.

I'm getting an error when I try to ReDim an array (Object Required). Can you offer any advice on what's wrong with my syntax.

The line in question is this:

ReDim genIntOutputAr1(1 To (inputAr.Count), 1 To 3)

The Code is:

Sub calcCoalInt()
   Dim inputArLastRow As Long, iRow As Long, oRow As Long, cRow As Long
   Dim rwNum As Long, colNum As Long, ub1 As Long, lb1 As Long, ub2 As Long, lb2 As Long
   Dim currentDepth As Double, shoeDepth As Double, openCutoff As Double, casedCutoff As Double
   Dim currentLSD As Double, currentSSD As Double
   Dim inputAr As Variant, finalOpenAr As Variant, finalCasedAr As Variant
   Dim inputOpenAr1() As Double, inputOpenAr() As Double
   Dim inputCasedAr1() As Double, inputCasedAr() As Double
   Dim inputSheet As Worksheet
   Dim outputSheet As Worksheet
   
   'Generate array of wireline data
   Set inputSheet = Worksheets("Wireline")
   Set outputSheet = Worksheets("Coal Calculator")
   inputArLastRow = inputSheet.Cells(Rows.Count, 2).End(xlUp).Row
   With inputSheet
       inputAr = .Range(.Cells(2, 2), .Cells(inputArLastRow, 6)).Value
   End With
   
   'Generate arrays of coal intercepts in open and cased hole
   oRow = 1
   cRow = 1
   ReDim inputOpenAr1(1 To (inputArLastRow - 1))
   ReDim inputCasedAr1(1 To (inputArLastRow - 1))
   casedCutoff = outputSheet.Range("D2")
   openCutoff = outputSheet.Range("D3")
   shoeDepth = outputSheet.Range("D4")
   For iRow = 1 To (inputArLastRow - 1)
       currentLSD = inputAr(iRow, 4)
       currentSSD = inputAr(iRow, 5)
       currentDepth = inputAr(iRow, 1)
       If (currentDepth > shoeDepth) Then
           If (currentSSD > 0) Then
               If (currentSSD < openCutoff) Then
                   inputOpenAr1(oRow) = currentDepth
                   oRow = oRow + 1
               End If
           End If
       Else
           If (currentLSD > 0) Then
               If (currentLSD < casedCutoff) Then
                   inputCasedAr1(cRow) = currentDepth
                   cRow = cRow + 1
               End If
           End If
       End If
   Next iRow
   
   'ReDim arrays of coal intercepts in open and cased hole
   ReDim inputOpenAr(1 To (oRow - 1))
   For iRow = 1 To (oRow - 1)
       inputOpenAr(iRow) = inputOpenAr1(iRow)
   Next iRow
   ReDim inputCasedAr(1 To (cRow - 1))
   For iRow = 1 To (cRow - 1)
       inputCasedAr(iRow) = inputCasedAr1(iRow)
   Next iRow
   
   'Generate final output arrays
   finalOpenAr = genIntOutputAr(inputOpenAr)
   finalCasedAr = genIntOutputAr(inputCasedAr)
   
   'Write arrays to page
   lb1 = LBound(finalOpenAr, 1)
   lb2 = LBound(finalOpenAr, 2)
   ub1 = UBound(finalOpenAr, 1)
   ub2 = UBound(finalOpenAr, 2)
   rwNum = ub1 - lb1 + 1
   colNum = ub2 - lb2 + 1
   outputSheet.Range("B8").Resize(rwNum, colNum).Value = finalOpenAr
   lb1 = LBound(finalCasedAr, 1)
   lb2 = LBound(finalCasedAr, 2)
   ub1 = UBound(finalCasedAr, 1)
   ub2 = UBound(finalCasedAr, 2)
   rwNum = ub1 - lb1 + 1
   colNum = ub2 - lb2 + 1
   outputSheet.Range("G8").Resize(rwNum, colNum).Value = finalCasedAr
   
End Sub

Function genIntOutputAr(inputAr As Variant) As Variant
   Dim lastDepth As Double
   Dim oRow As Long
   Dim genIntOutputAr1() As Double
   Dim genIntOutputAr2() As Double
   
   'Generate final arrays
   oRow = 1
   lastDepth = inputAr(1)
   ReDim genIntOutputAr1(1 To (inputAr.Count), 1 To 3)
   genIntOutputAr1(oRow, 1) = inputAr(1)
   For iRow = 2 To (inputAr.Count)
       If Not (lastDepth = (inputAr(iRow) - 1)) Then
           genIntOutputAr1(oRow, 2) = lastDepth
           genIntOutputAr1(oRow, 3) = genIntOutputAr1(oRow, 2) - genIntOutputAr1(oRow, 1)
           oRow = oRow + 1
           genIntOutputAr1(oRow, 1) = inputAr(iRow)
       End If
       lastDepth = inputAr(iRow)
   Next iRow
   genIntOutputAr1(oRow, 2) = inputAr(inputAr.Count)
   genIntOutputAr1(oRow, 3) = genIntOutputAr1(oRow, 2) - genIntOutputAr1(oRow, 1)
   
   'ReDim final arrays
   ReDim genIntOutputAr2(1 To oRow, 1 To 3)
   For iRow = 1 To oRow
       genIntOutputAr2(iRow) = genIntOutputAr1(iRow)
   Next iRow
   genIntOutputAr = genIntOutputAr2
End Function

Kind Regards,
Nick

Answer
Nick,


inputar is an array. It has no properties, particularly not a count property as you are trying to do.  Buy trying to use a property, excel thinks inputar should be an object with a count property, but it isn't.

I found at least 3 places where you use that construct.  I would replace inputar.count with Ubound(inputar) and that should work, but I don't know if that is what you want.


--
Regards,
Tom Ogilvy