If function in VBA

alring · Dec 7th 2005

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.

Please someone help me.

Alring

Dave Hawley · Dec 7th 2005

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

fengore · Dec 7th 2005

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"

alring · Dec 7th 2005

Re: If function in VBA

Hi Dave

Thank you for your reply. :thanx:
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

fengore · Dec 7th 2005

Re: If function in VBA

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

fengore · Dec 7th 2005

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

alring · Dec 7th 2005

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:

Code

Sub maerkedage()


    With Sheet1
    If UCase(.Cells(1, 1)) = "N" And _
       UCase(.Cells(2, 1)) = "N" And _
       UCase(.Cells(3, 1)) = "N" Then
    .Cells(1, 4) = "Yes"
End If
End With
End Sub

Display More

Again. Thank you :thanx:

Alring

fengore · Dec 7th 2005

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.

If function in VBA

Participate now!

Share