Prev: User Account
Next: List Listbox items in column by macro
From: Simon on 16 Mar 2010 02:22
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
From: Jacob Skaria on 16 Mar 2010 02:37
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
 | 
Pages: 1
Prev: User Account
Next: List Listbox items in column by macro