CODEWITHSUNDEEP
vbaexcel
user6652991
user6652991

Reputation: 1

custom made functions in VBA

I have a list of films and their durations in minutes. I wish to create a custom function whereby the user selects the range of durations i.e. one or more cells. The function converts the duration in minutes into " x hours and y minutes form ".
I have created the following function but it does not seem to take more than 1 cell reference at a time. 

Function Saikatrealtime(cell As Range) As String

    Dim r As String

    r = Int(cell.Value / 60) & " hours " & " & " & cell.Value Mod 60 & " minutes "
    Saikatrealtime = r

End Function


Sub realtimesof_film()

    Dim rng As Range
    Dim t As String

    s1.Activate
    Set rng = Application.InputBox(prompt:=" enter range ", Type:=8)

    t = Saikatrealtime(rng)
    rng.Offset(0, 2).Value = t

End Sub

Whenever I give a reference to rng as 2 or more cells, it gives the error type mismatch at the line

r = Int(cell.Value / 60) & " hours " & " & " & cell.Value Mod 60 & " minutes "

Can anyone please tell me how I can modify the code so that the function runs across multiple cell references given to rng?

Upvotes: 0

Views: 66

Answers (2)

user6432984
user6432984

Reputation:

There is no need for the custom function Saikatrealtime. You can use Format instead. The key is that you have to escape certain letters in the text using a backslash \

Sub realtimesof_film()

    Dim rng As Range
    Dim c As Range

    Set rng = Application.InputBox(prompt:=" enter range ", Type:=8)

    For Each c In rng
        c.Offset(0, 2).Value = Format(c.Value, "HH \hour\s & MM \mi\nute\s ")
    Next

End Sub

Upvotes: 0

RGA
RGA

Reputation: 2607

Unless you change the formula to return an array of values, you'll have to modify the subroutine code to reference multi-cell ranges. Adding a loop of the cells within the range will work. Add this to your subroutine:

Dim Cell As Range

For Each Cell In rng.Cells
    Cell.Offset(0, 2).Value = Saikatrealtime(Cell)
Next Cell

Upvotes: 1

Related Questions