If then Else in macro

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sandy - 23 Apr 2007 14:26 GMT

I am trying in vain to incorporate an If Then Else into a macro; the basis
is as follows

Sheet Name = 'Search'

If -Cells in the ranges (B5:F5) AND (B8:F8) AND (C8) AND (F9) = ""
Then
Do this
Else
Do that
End If

The bit between 'If' and 'Then' eludes me. Any offers?

Sandy

Reply to this Message

JE McGimpsey - 23 Apr 2007 14:39 GMT

One way:

With Sheets("Search").Range("B5:F5,B8:F8,F9")
If Application.CountA(.Cells) <> .Count Then
'Do This
Else
'Do That
End If
End With

> I am trying in vain to incorporate an If Then Else into a macro; the basis
> is as follows
[quoted text clipped - 11 lines]
>
> Sandy

Reply to this Message

Barb Reinhardt - 23 Apr 2007 14:40 GMT

What do you want to do? Let's say you want to change the values of those
cells.

Cells(RowNum, ColNum).value = 10 '<~~~changes the value in the cell at the
location given
Range("C8").value = 10 '<~~changes the value in C8 to 10

It's tough to know how to answer without a more specific question.

> I am trying in vain to incorporate an If Then Else into a macro; the basis
> is as follows
[quoted text clipped - 11 lines]
>
> Sandy

Reply to this Message

Dave Peterson - 23 Apr 2007 14:47 GMT

Are you checking to see if the cells are empty?

Dim myRng As Range
Set myRng = Worksheets("Sheet1").Range("b5:f5,B8:f8,F9")
If Application.CountA(myRng) = 0 Then
'all empty
Else
'not all empty
End If

=counta() will count any cell that has a formula that evaluates to "".

And C8 is part of B8:F8, so it doesn't need to be checked twice.

> I am trying in vain to incorporate an If Then Else into a macro; the basis
> is as follows
[quoted text clipped - 11 lines]
>
> Sandy

Signature

Dave Peterson

Reply to this Message