I am new to VBA and I am trying to compute a sum of some data over the past month. Here is my code :

Function Monthly_volume_given_velocity() As Double
Dim velocity As Double
Dim ind_vit As Integer
Dim ind_today As Integer
velocity= ActiveCell.Offset(0, -1).Value

With Sheets("2022")
ind_vel = .Range("A5:A13").Find(What:=velocity).Row
Dim rng As Range
rng = .Rows("1").Find(What:=Date, LookIn:=xlFormulas)
If Not rng Is Nothing Then ind_today = rng.Column: Debug.Print ind_today 

Monthly_volume_given_velocity = Application.WorksheetFunction.Sum(.Range(.Cells(ind_vel , ind_today - 30), .Cells(ind_vel , ind_today )))
End With
End Function

I get a #Value! error using this code, although running it in debug mode I get the right value for Monthly_volume_given_velocity.

Replacing ind_today in the code with its expected value gives no error, so I guess it comes from the Range.Find procedure. I got the If Not rng... procedure from a different thread but it does not seem to solve this issue.

Does anyone know what I did wrong?

CodePudding user response：

When a UDF runs on any error, it will return an error that is displayed as #VALUE in your sheet.
Now it is hard to tell what exactly fails in the function, but I see some issues:

• A UDF should never rely on ActiveCell (or ActiveSheet). Excel handles the recalculation, and it can be triggered if the cell with the formula is not active. Pass the cell as parameter.

• Find for Date can be tricky, see Range.Find on a Date That is a Formula. I would suggest you search for the date manually.

• You miss a Set when you assign the result of the Second Find.

• You check if rng is not Nothing (so Find returned something). However, if it didn't find anything, int_today will be 0 and the following Sum will get an invalid range as parameter.

I reworked your function. Of course, the Debug.Print-statements can be deleted once the function works. As said, you will have to pass the cell as parameter in the formula, eg =Monthly_volume_given_velocity(B1).

Function Monthly_volume_given_velocity(cell As Range) As Double
    Dim velocity As Double
    Dim ind_vel As Long
    Dim ind_today As Long
    velocity = cell.Offset(0, -1).Value
    Debug.Print "Vel: " & velocity
        
    With Sheets("2022")
        ' Find Velocity Row
        On Error Resume Next
        ind_vel = .Range("A5:A13").Find(What:=velocity).row
        On Error GoTo 0
        If ind_vel = 0 Then Exit Function
        Debug.Print "ind_vel: " & ind_vel
        
        ' Find Date column
        Dim dates As Variant, lastCol As Long
        lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        Debug.Print "lastcol: " & lastCol
        dates = .Cells(1, 1).Resize(1, lastCol)
        For ind_today = 1 To lastCol
            Debug.Print ind_today, dates(1, ind_today)
            If dates(1, ind_today) = Date Then Exit For
        Next
        If ind_today > lastCol Then
            Debug.Print "today not found"
            Exit Function ' date not found
        End If
        Debug.Print ind_today
        
        ' Calculate value
        Dim rng As Range
        Set rng = Range(.Cells(ind_vel, ind_today - 5), .Cells(ind_vel, ind_today))
        Debug.Print rng.Address
        Monthly_volume_given_velocity = Application.WorksheetFunction.Sum(rng)
    End With
End Function

CodePudding user response：

A range is an object and thus has to be defined using Set, so it has to be

Set rng = [...]

Plus a remark regarding the .Find method, in case you don't know: The args (LookIn, LookAt etc.) are static, i.e. they are remembered from the last call if not otherwise defined in a new call. This also includes using the Find dialogue in the user interface. To be on the safe side, one should always provide all args with each call.