I have a program Sub
which works well. I want to convert it into a custom Function, but when I use this function in Excel an error (#VALUE!
) occurs
Function ТридцатьТРи(Diapozon As Integer)
'для п/пр
Dim k, n As Integer
Dim parRange As Range
Set parRange = Range("Diapozon")
k = 0
n = 0
For Each Cell In parRange.Rows
If Cell.Offset(0, 1).Value = 1 And k = -1 Then
n = n - 1
End If
If Cell.Value = 1 And k = -1 Then
n = n + 1
End If
If Cell.Value = 1 Then
k = k + 1
If k = 2 Then
k = -1
End If
End If
If Cell.Value = 2 Or Cell.Value = 3 Then
k = 0
End If
Next Cell
ТридцатьТРи = n
End Function
Try the UDF code below (not sure about what you are trying to achieve with your logics inside the UDF), but it works (not getting #VALUE!
).
Since you want to pass a Range
object to the UDF (according to your screen-shot), you need to define it also in your Function
code.
Code
Function cyrilic(Diapozon As Range) As Long
Dim k As Long, n As Long
Dim C As Range
k = 0
n = 0
For Each C In Diapozon.Rows
If C.Offset(0, 1).Value = 1 And k = -1 Then
n = n - 1
End If
If C.Value = 1 And k = -1 Then
n = n + 1
End If
If C.Value = 1 Then
k = k + 1
If k = 2 Then
k = -1
End If
End If
If C.Value = 2 Or C.Value = 3 Then
k = 0
End If
Next C
cyrilic = n
End Function