Passing range as argument
 

Hi NG

I have made a function, that works perfectly allright, doing some
calculation based on the contents of two ranges, but I would like to pass
the two ranges as arguments in stead of "hard-coding" them into the
function. However i can'øt get it to work. This is the working code.

Function JaNej() As String
n = 0
m = 0
Application.Volatile
For Each c In Range("d28:i28").Cells
If c.Value = "nej" Then
n = n + 1
Else
n = n + 0
End If
Next c
For Each c In Range("d30:i30").Cells
If c.Value = "nej" Then
m = m + 1
Else
m = m + 0
End If
Next c
If n 0 Then
JaNej = "Nej"
Else
JaNej = "Ja"
End If
End Function

Jan

Passing range as argument
 

Hi

try this. Alos it's better the declare your variables
explicitly

Function JaNej(rng1 As Range, rng2 As Range) As String
Dim c As Range
Dim n As Integer, m As Integer

n = 0
m = 0
Application.Volatile
For Each c In rng1.Cells
If c.Value = "nej" Then
n = n + 1
Else
n = n + 0
End If
Next c
For Each c In rng2.Cells
If c.Value = "nej" Then
m = m + 1
Else
m = m + 0
End If
Next c
If n 0 Then
JaNej = "Nej"
Else
JaNej = "Ja"
End If
'clean up
Set c = Nothing
End Function
-----Original Message-----
Hi NG

I have made a function, that works perfectly allright,
doing some
calculation based on the contents of two ranges, but I
would like to pass
the two ranges as arguments in stead of "hard-coding"
them into the
function. However i can'øt get it to work. This is the
working code.

Function JaNej() As String
n = 0
m = 0
Application.Volatile
For Each c In Range("d28:i28").Cells
If c.Value = "nej" Then
n = n + 1
Else
n = n + 0
End If
Next c
For Each c In Range("d30:i30").Cells
If c.Value = "nej" Then
m = m + 1
Else
m = m + 0
End If
Next c
If n 0 Then
JaNej = "Nej"
Else
JaNej = "Ja"
End If
End Function

Jan



.

Passing range as argument
 

Thanks. I know about the declaration part, but I just did it the "easy" way.
But now it works.

Jan
"Richard Daniels" skrev i en meddelelse
...
Hi

try this. Alos it's better the declare your variables
explicitly

Function JaNej(rng1 As Range, rng2 As Range) As String
Dim c As Range
Dim n As Integer, m As Integer

n = 0
m = 0
Application.Volatile
For Each c In rng1.Cells
If c.Value = "nej" Then
n = n + 1
Else
n = n + 0
End If
Next c
For Each c In rng2.Cells
If c.Value = "nej" Then
m = m + 1
Else
m = m + 0
End If
Next c
If n 0 Then
JaNej = "Nej"
Else
JaNej = "Ja"
End If
'clean up
Set c = Nothing
End Function
-----Original Message-----
Hi NG

I have made a function, that works perfectly allright,
doing some
calculation based on the contents of two ranges, but I
would like to pass
the two ranges as arguments in stead of "hard-coding"
them into the
function. However i can'øt get it to work. This is the
working code.

Function JaNej() As String
n = 0
m = 0
Application.Volatile
For Each c In Range("d28:i28").Cells
If c.Value = "nej" Then
n = n + 1
Else
n = n + 0
End If
Next c
For Each c In Range("d30:i30").Cells
If c.Value = "nej" Then
m = m + 1
Else
m = m + 0
End If
Next c
If n 0 Then
JaNej = "Nej"
Else
JaNej = "Ja"
End If
End Function

Jan



.

Passing range as argument
 

You can also make this a little cleaner. Since you're adding 0 to
the value, you can eliminate the Elses:

Function JaNej(rng1 As Range, rng2 As Range) As String
Dim c As Range
Dim n As Integer, m As Integer
Application.Volatile
n = 0
m = 0
For Each c In rng1.Cells
If c.Value = "nej" Then n = n + 1
Next c
For Each c In rng2.Cells
If c.Value = "nej" Then m = m + 1
Next c
If n 0 Then
JaNej = "Nej"
Else
JaNej = "Ja"
End If
'clean up
Set c = Nothing
End Function

Also, based on the above, since you're passing the ranges as
arguments, I don't think you need the Application.Volatile any
longer.

You could also use the built-in COUNTIF() function:

Function JaNej(rng1 As Range, rng2 As Range) As String
Dim c As Range
Dim n As Integer, m As Integer
n = Application.CountIf(rng1, "nej")
m = Application.CountIf(rng2, "nej")
If n 0 Then
JaNej = "Nej"
Else
JaNej = "Ja"
End If
'clean up
Set c = Nothing
End Function


In article ,
"Richard Daniels" wrote:

i

try this. Alos it's better the declare your variables
explicitly

Function JaNej(rng1 As Range, rng2 As Range) As String
Dim c As Range
Dim n As Integer, m As Integer

n = 0
m = 0
Application.Volatile
For Each c In rng1.Cells
If c.Value = "nej" Then
n = n + 1
Else
n = n + 0
End If
Next c
For Each c In rng2.Cells
If c.Value = "nej" Then
m = m + 1
Else
m = m + 0
End If
Next c
If n 0 Then
JaNej = "Nej"
Else
JaNej = "Ja"
End If
'clean up
Set c = Nothing
End Function