What wrong is this macro? When I try to run following macro, it shows error on f

Question

What wrong is this macro?

When I try to run following macro, it shows error on following codes,

Does anyone have any suggestions?
Thanks in advance for any suggestions

Sub test()
Dim i As Long

i = ABC(Range("E2:E100"), 9)
End Sub

Function ABC(Mycell As Range, k As Integer)

add_r = Mycell.Address
UpValue = 0
DownValue = 0

For i = 1 To (k - 1)
j = k - i
If Range(add_r).Offset(-j, 0) < Range(add_r).Offset(-j + 1, 0) Then
UpValue = (UpValue + Range(add_r).Offset(-j + 1, 0) - Range(add_r).Offset(-j, 0))
End If
If Range(add_r).Offset(-j, 0) > Range(add_r).Offset(-j + 1, 0) Then
DownValue = (DownValue + Range(add_r).Offset(-j + 1, 0) - Range(add_r).Offset(-j, 0))
End If
Next i
...

End Function

Explanation / Answer

Range(add_r) is the same as MyCell.

You pass a multi-cell range to MyCell, so Range(add_r) and Range(add_r).Offset(-j, 0) are multi-cell ranges too.

A multi-cell range does not have a value, only a single cell has a value.

Moreover, you pass k = 9 to the function, so j = 8.

Range(add_r).Offset(-7, 0) is not valid since the range starts in row 2.

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.