Get Range object from Cell Row and Column No.
 

Hi,

I'm using Excel 2003, I have a function which I want it to return a range
object which points to a cell, the cell is identified by the code which
determins which column number and row number make up the Cells RowIndex and
ColumnIndex attributes. For some reason I keep receiving an
application-defined or object-defined error, my code is below, any advice
would be appreciated:

Function GetJournalInsertRange(ColumnHeader As String) As Range
Dim ColumnNumber As Integer
Dim RowNumber As Integer
ColumnNumber = 0
For Each header In Range("JournalHdr")
If ColumnHeader = header.Text Then
ColumnNumber = header.Column
End If
Next
RowNumber = Range("A65536").End(xlUp).Offset(1, ColumnNumber - 1).Row
Set GetJournalInsertRange = ActiveSheet.Range(Cells(RowNumber,
ColumnNumber))

End Function

Thanks.
Simon

Get Range object from Cell Row and Column No.
 

Try changing the last line to
Set GetJournalInsertRange = ActiveSheet.Cells(RowNumber,ColumnNumber)

OR try using this function...which do not loop

Function GetJournalInsertRange(ColumnHeader As String) As Range
Dim lngCol As Long, lngRow As Long
On Error Resume Next
lngCol = Range("JournalHdr").Find(ColumnHeader, _
LookIn:=xlValues, LookAt:=xlWhole).Column
If lngCol 0 Then
lngRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
Set GetJournalInsertRange = ActiveSheet.Cells(lngRow, lngCol)
End If

End Function

--
Jacob


"Simon" wrote:

Hi,

I'm using Excel 2003, I have a function which I want it to return a range
object which points to a cell, the cell is identified by the code which
determins which column number and row number make up the Cells RowIndex and
ColumnIndex attributes. For some reason I keep receiving an
application-defined or object-defined error, my code is below, any advice
would be appreciated:

Function GetJournalInsertRange(ColumnHeader As String) As Range
Dim ColumnNumber As Integer
Dim RowNumber As Integer
ColumnNumber = 0
For Each header In Range("JournalHdr")
If ColumnHeader = header.Text Then
ColumnNumber = header.Column
End If
Next
RowNumber = Range("A65536").End(xlUp).Offset(1, ColumnNumber - 1).Row
Set GetJournalInsertRange = ActiveSheet.Range(Cells(RowNumber,
ColumnNumber))

End Function

Thanks.
Simon