mae0429
06-09-2008, 10:26 AM
I can't quite seem to understand what is happening with my code that causes a run-time error 91 (object variable or with block variable not set). The goal of the function is to basically find where the headings start and stop on the Excel sheet and then return that range of cells. Thank you for any help you may have!
Here is my code:
Option Explicit
Function HeaderRange() As Range
Dim endColumn, counter, i, j, k, startRow As Integer
Dim cellCheck, cellStart As String
' Get the number of headings
endColumn = 0
For i = 1 To 10
counter = 0
For j = 1 To 100
cellCheck = Cells(i, j)
If Not cellCheck = "" Then
counter = counter + 1
Else
Exit For
End If
Next j
If counter > endColumn Then
endColumn = counter - 1
End If
Next i
' Get the starting row number
For k = 1 To 100
cellStart = Cells(k, 2).Value
If Not cellStart = "" Then
startRow = k
Exit For
End If
Next k
HeaderRange = Range(Cells(startRow, 1), Cells(startRow, endColumn))
End Function
Here is my code:
Option Explicit
Function HeaderRange() As Range
Dim endColumn, counter, i, j, k, startRow As Integer
Dim cellCheck, cellStart As String
' Get the number of headings
endColumn = 0
For i = 1 To 10
counter = 0
For j = 1 To 100
cellCheck = Cells(i, j)
If Not cellCheck = "" Then
counter = counter + 1
Else
Exit For
End If
Next j
If counter > endColumn Then
endColumn = counter - 1
End If
Next i
' Get the starting row number
For k = 1 To 100
cellStart = Cells(k, 2).Value
If Not cellStart = "" Then
startRow = k
Exit For
End If
Next k
HeaderRange = Range(Cells(startRow, 1), Cells(startRow, endColumn))
End Function