What is wrong with this function?

M

Manasses

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
 
M

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
Click to expand...

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)
 
D

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
[email protected]
Manasses said:
Hello, I am using VBA in Excel XP with Windows 2000.

The function below is designed to locate the last used column in the
Click to expand...
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.
 
C

chris

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
 
Top