What is wrong with this function?

[Guest]

Hello, I am using VBA in Excel XP with Windows 2000

The function below is designed to locate the last used column in the sheet. If the sheet is blank, the function should return the integer zero. The problem is when the sheet is blank it generates the error "Object variable or with block not set." I've looked at it so long I can't see what is wrong. NOTE: Lines 2 and 3 should be on the same line

Dim intColumn As Intege
intColumn = ActiveSheet.UsedRange.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Colum
If IsEmpty(intColumn) Then ColumnLastFilled =
If Not IsEmpty(intColumn) Then ColumnLastFilled = intColum

Thanks in advance

 

[Melanie Breden]

Hi Manasses,

The function below is designed to locate the last used column in the sheet. If the sheet is blank, the function should return
the integer zero. The problem is when the sheet is blank it generates the error "Object variable or with block not set." I've
looked at it so long I can't see what is wrong. NOTE: Lines 2 and 3 should be on the same line.

Dim intColumn As Integer
intColumn = ActiveSheet.UsedRange.Find(What:="*", SearchOrder:=xlByColumns,
SearchDirection:=xlPrevious).Column
If IsEmpty(intColumn) Then ColumnLastFilled = 0
If Not IsEmpty(intColumn) Then ColumnLastFilled = intColumn

try this:

On Error Resume Next
intColumn = ActiveSheet.UsedRange.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
ColumnLastFilled = IIf(Err > 0, 0, intColumn)

--
Mit freundlichen Grüssen

Melanie Breden
- Microsoft MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)

 

[Don Guillett]

found here
http://homepages.paradise.net.nz/~robree/excel/

Sub lastused()
Dim lngLastRow As Long, lngLastCol As Long

On Error Resume Next
lngLastRow = 1: lngLastCol = 1
With ActiveSheet.UsedRange
lngLastRow = .Find("*", .Cells(1), xlFormulas, _
xlWhole, xlByRows, xlPrevious).Row
lngLastCol = .Find("*", .Cells(1), xlFormulas, _
xlWhole, xlByColumns, xlPrevious).Column
.Cells(lngLastRow, lngLastCol).Select
End With
MsgBox lngLastRow
MsgBox lngLastCol
End Sub

--
Don Guillett
SalesAid Software
(e-mail address removed)

Hello, I am using VBA in Excel XP with Windows 2000.

The function below is designed to locate the last used column in the

sheet. If the sheet is blank, the function should return the integer zero.
The problem is when the sheet is blank it generates the error "Object
variable or with block not set." I've looked at it so long I can't see what
is wrong. NOTE: Lines 2 and 3 should be on the same line.

 

[Guest]

1.An Integer data type = 0 when initialized so it can't be Empty
2.Find returns a range object, if it not found, it returns Nothing , so your code is literally saying 'intColumn = Nothing.Column
test the Find first

Dim intColumn As Integer, x as rang
Set x =ActiveSheet.UsedRange.Find(What:="*", SearchOrder:=xlByColumns,SearchDirection:=xlPrevious
If Not x Is Nothing Then intColumn = x.Colum
If intColumn= 0 Then ColumnLastFilled =
If Not intColumn= 0 Then ColumnLastFilled = intColum

----- Manasses wrote: ----

Hello, I am using VBA in Excel XP with Windows 2000

The function below is designed to locate the last used column in the sheet. If the sheet is blank, the function should return the integer zero. The problem is when the sheet is blank it generates the error "Object variable or with block not set." I've looked at it so long I can't see what is wrong. NOTE: Lines 2 and 3 should be on the same line

Dim intColumn As Intege
intColumn = ActiveSheet.UsedRange.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Colum
If IsEmpty(intColumn) Then ColumnLastFilled =
If Not IsEmpty(intColumn) Then ColumnLastFilled = intColum

Thanks in advance