 # If function in VBA

• Hi there

I have (for me) a big problem. In the attached document I have a formula in cell D7. D7 links to cell A7:A9. And it works. Now I want that I can do the same thing in VBA but the result should be in cell D1. D1 links to cell A1:A3.

I have made the formula in cell D7 to explain what it is I want . I'm not going to use that one in the future but only the VBA code.

Alring

## Files

• Re: If function in VBA

Hi,

The VBA equivilent of your Worksheet IF function would be

Code
``````If UCase(Range("A7")) = "N" And _
UCase(Range("A8")) = "N" And _
UCase(Range("A9")) = "N" Then

'TRUE CODE
Else

'FALSE CODE
End If``````
• Re: If function in VBA

Code
``If (Application.WorksheetFunction.CountIf(Range("a1:a3"), "=n") = 3) Then Cells(1, 2) = "yes" Else Cells(1, 2) = "no"``

[COLOR="Purple"]Eternity is a terrible thought. I mean, where's it going to end??[/COLOR]

• Re: If function in VBA

Hi Dave

There is just one small problem. I would like to use .cells(1,1) and .cells(2,1) and .cells(3,1) instead of A1 and A2 and A3. I have tries to change your code to that but I can't get to work. Maybe it's because it's not possible. or maybe I write the code wrongly. The last thing is very possible. :? Is it possible to use .cells instead of letter and number for the cells??

Alring

• Re: If function in VBA

There are always a few ways to go when it comes to writing this type of code.

[COLOR="Purple"]Eternity is a terrible thought. I mean, where's it going to end??[/COLOR]

• Re: If function in VBA

To change a range to use cells, simply use it twice.

Code
``range(cells(1,1), cells(1,3)).select``

[COLOR="Purple"]Eternity is a terrible thought. I mean, where's it going to end??[/COLOR]

• Re: If function in VBA

Hi Fengore and Dave

I have solve the problem. Both of you thnak you for your help. :thanx:

The code work when it look like this:

Again. Thank you :thanx:

Alring

• Re: If function in VBA

slightly more complicated is the following:

Code
``````ColNo = 1: RowNo = 4
ColLetter = Chr(ColNo + 64)
Range(ColLetter & RowNo).Select``````

Glad you got what you needed. Bye.

[COLOR="Purple"]Eternity is a terrible thought. I mean, where's it going to end??[/COLOR]

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!