how to write an if/then block

[Janis]

The macro without the comments works, the problem is when I delete the first
row while testing I noticed if I run the macro a second time I get an error
since the row is already deleted. Since the user could run it twice
accidentally, I decided to put some error checking to make sure that first
row holds a date before I delete that pesky date row. I want to save the
date variables and put them in later.

I have a problem with the Else block. I want the if block to go down to the
else statement but it thinks it is over with the if then line. I want the
then part to extend all the way to the else.
thanks,

Sub deleteDateRow1()
Dim FrReptDate As Date
Dim ToReptDate As Date
Dim Rng As Range
Rng = Range("A1")
If Rng.Value.Date Then FrReptDate = Rng.Value
'With Range("A1")
'FrReptDate = .Value
ToReptDate = Rng.Offset(0, 1).Value
Rng.EntireRow.Delete Shift:=xlUp
'End With
Debug.Print FrReptDate, ToReptDate
Else
Exit Sub
End Sub

 

[JE McGimpsey]

I have a problem with the Else block. I want the if block to go down to the
else statement but it thinks it is over with the if then line. I want the
then part to extend all the way to the else.

A few problems here:

1) Rng is declared as a Range object variable, so

Rng = Range("A1")

will result in an "Object variable or With block variable not set"
error, since it attempts to use Rng without ever having set it to a
range object. Since there's no object property defined, the statement
above is interpreted using the Range object's default property, .Value,
as in

Rng.Value = Range("A1").Value

which won't work since VBA doesn't know what Rng refers to.

OTOH, using Set would assign the active sheet's Range("A1") to the Rng
range object variable:

Set Rng = Range("A1")

2) If...Then(...Else) statements can have one of two forms. The first is
all on one line:

If x Then y

or

If x Then y Else z

the second is separating clauses

If x Then
y
Else
z
End if

The If statement in the sub is of the first form, so the compiler sees
it as a complete statement and everything after that as stand-alone
statements, which is why the Else statement throws the error.

3) Not sure what

Rng.Value.Date

is supposed to mean. .Value is a *property* of the Rng range object, not
an object itself, therefore it can't have a .Date property. If you are
trying to verify that Rng.Value contains a date, use the IsDate()
function:

If IsDate(Rng.Value) Then
FrReptDate = Rng.Value

4) In your code, there's no reason for the else statement, since the
only thing it does is exit the sub. Given that, there's no reason to use
the Rng variable - simply use With Range("A1")...

I think this is more what you want:


Public Sub deleteDateRow1()
Dim FrReptDate As Date
Dim ToReptDate As Date

With Range("A1")
If IsDate(.Value) Then
FrReptDate = .Value
ToReptDate = .Offset(0, 1).Value
.EntireRow.Delete Shift:=xlUp
End If
End With
End Sub

But if you want to use the Rng variable and the Else statement:

Public Sub deleteDateRow1()
Dim FrReptDate As Date
Dim ToReptDate As Date
Dim Rng As Range

Set Rng = Range("A1")
With Rng
If IsDate(.Value) Then
FrReptDate = .Value
ToReptDate = .Offset(0, 1).Value
.EntireRow.Delete Shift:=xlUp
Else
Exit Sub
End If
End With
End Sub

 

[Janis]

Thanks, the explanations helped.

A few problems here:

1) Rng is declared as a Range object variable, so

Rng = Range("A1")

will result in an "Object variable or With block variable not set"
error, since it attempts to use Rng without ever having set it to a
range object. Since there's no object property defined, the statement
above is interpreted using the Range object's default property, .Value,
as in

Rng.Value = Range("A1").Value

which won't work since VBA doesn't know what Rng refers to.

OTOH, using Set would assign the active sheet's Range("A1") to the Rng
range object variable:

Set Rng = Range("A1")

2) If...Then(...Else) statements can have one of two forms. The first is
all on one line:

If x Then y

or

If x Then y Else z

the second is separating clauses

If x Then
y
Else
z
End if

The If statement in the sub is of the first form, so the compiler sees
it as a complete statement and everything after that as stand-alone
statements, which is why the Else statement throws the error.

3) Not sure what

Rng.Value.Date

is supposed to mean. .Value is a *property* of the Rng range object, not
an object itself, therefore it can't have a .Date property. If you are
trying to verify that Rng.Value contains a date, use the IsDate()
function:

If IsDate(Rng.Value) Then
FrReptDate = Rng.Value

4) In your code, there's no reason for the else statement, since the
only thing it does is exit the sub. Given that, there's no reason to use
the Rng variable - simply use With Range("A1")...

I think this is more what you want:


Public Sub deleteDateRow1()
Dim FrReptDate As Date
Dim ToReptDate As Date

With Range("A1")
If IsDate(.Value) Then
FrReptDate = .Value
ToReptDate = .Offset(0, 1).Value
.EntireRow.Delete Shift:=xlUp
End If
End With
End Sub

But if you want to use the Rng variable and the Else statement:

Public Sub deleteDateRow1()
Dim FrReptDate As Date
Dim ToReptDate As Date
Dim Rng As Range

Set Rng = Range("A1")
With Rng
If IsDate(.Value) Then
FrReptDate = .Value
ToReptDate = .Offset(0, 1).Value
.EntireRow.Delete Shift:=xlUp
Else
Exit Sub
End If
End With
End Sub