Is Empty
 

Trying to write code that moves some data up a row but having troubles
due to ignorance. The error message on compile is: "Expected Array"
for the IsEmpty statement. Can someone help bridge this problem?

Here's the code:

Thanks, Cliff

Dim OneRowUp As Range
Dim MyRange As Range
Dim ActiveCell As Range
Dim IsEmpty As Boolean
Dim IsNumeric As Boolean
Range("C1").Select
With Selection
Do While Not IsEmpty(ActiveCell.Offset(0, -2))
If Application.WorksheetFunction.IsNumber(ActiveCell. Value) =
True Then
Set MyRange = Range(ActiveCell, ActiveCell.Offset(0, 8))
Set OneRowUp = ActiveCell.Offset(-1, 1)
MyRange.Cut Destination:=OneRowUp
ActiveCell.Offset(2, -1).Select
Else
ActiveCell.Offset(1, 0).Select
End If
If IsEmpty(ActiveCell.Offset(0, -2)) Then Exit Sub
End If
Loop
End With
End Sub


--
Lift Off
------------------------------------------------------------------------
Lift Off's Profile: http://www.excelforum.com/member.php...fo&userid=8249
View this thread: http://www.excelforum.com/showthread...hreadid=543870

Is Empty
 

Your problem lies in the fact that you have a variable name
IsEmpty, which is also a VBA Function. Change the name of your
IsEmpty variable.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Lift Off"
wrote in
message
...

Trying to write code that moves some data up a row but having
troubles
due to ignorance. The error message on compile is: "Expected
Array"
for the IsEmpty statement. Can someone help bridge this
problem?

Here's the code:

Thanks, Cliff

Dim OneRowUp As Range
Dim MyRange As Range
Dim ActiveCell As Range
Dim IsEmpty As Boolean
Dim IsNumeric As Boolean
Range("C1").Select
With Selection
Do While Not IsEmpty(ActiveCell.Offset(0, -2))
If Application.WorksheetFunction.IsNumber(ActiveCell. Value) =
True Then
Set MyRange = Range(ActiveCell, ActiveCell.Offset(0, 8))
Set OneRowUp = ActiveCell.Offset(-1, 1)
MyRange.Cut Destination:=OneRowUp
ActiveCell.Offset(2, -1).Select
Else
ActiveCell.Offset(1, 0).Select
End If
If IsEmpty(ActiveCell.Offset(0, -2)) Then Exit Sub
End If
Loop
End With
End Sub


--
Lift Off
------------------------------------------------------------------------
Lift Off's Profile:
http://www.excelforum.com/member.php...fo&userid=8249
View this thread:
http://www.excelforum.com/showthread...hreadid=543870

Is Empty
 

Get rid of the Dim IsEmpty As Boolean
change do while to: Do While Isempty(ActiveCell.Offset(0, -2)) = False
also, you have an extra End if after If Isempty(ActiveCell.Offset(0
-2)) Then Exit Sub
Hope this helps.



Lift Off Wrote:
Trying to write code that moves some data up a row but having trouble
due to ignorance. The error message on compile is: "Expected Array
for the IsEmpty statement. Can someone help bridge this problem?

Here's the code:

Thanks, Cliff

Dim OneRowUp As Range
Dim MyRange As Range
Dim ActiveCell As Range
Dim IsEmpty As Boolean
Dim IsNumeric As Boolean
Range("C1").Select
With Selection
Do While Not IsEmpty(ActiveCell.Offset(0, -2))
If Application.WorksheetFunction.IsNumber(ActiveCell. Value)
True Then
Set MyRange = Range(ActiveCell, ActiveCell.Offset(0, 8))
Set OneRowUp = ActiveCell.Offset(-1, 1)
MyRange.Cut Destination:=OneRowUp
ActiveCell.Offset(2, -1).Select
Else
ActiveCell.Offset(1, 0).Select
End If
If IsEmpty(ActiveCell.Offset(0, -2)) Then Exit Sub
End If
Loop
End With
End Su

--
kwiklearne
-----------------------------------------------------------------------
kwiklearner's Profile: http://www.excelforum.com/member.php...fo&userid=3190
View this thread: http://www.excelforum.com/showthread.php?threadid=54387