Our training courses

Other training resources

Our training venues

Why we are different

Details for ryan051788

ryan051788 has participated in the following threads:

Added by ryan051788 on 10 Jun 2018 at 02:40

Hi there,

I tried to do the same as what you have here on your tutorial. I just cannot find out why on the calculation of time lengths for the movies, the last row of result is showing #N/A error for me.

The file is here: https://www.dropbox.com/s/kl6yonheorqocm6/VBA%20Arrays%20sample%20with%20calculation.xlsm?dl=0


Can you please check my code?

Option Explicit

Sub CalculateWithArray()

    Dim FilmLengths() As Variant
    Dim Answers() As Variant
    Dim Dimension1 As Long, Counter As Long
    Range("f3", Range("g3").End(xlDown)).ClearContents
    ' Assign values of range D to the array FilmLengths
    FilmLengths = Range("d3", Range("d3").End(xlDown))
    ' Assign the number of values of array FilmLengths to the variable Dimension1
    Dimension1 = UBound(FilmLengths, 1)
    ' Resize the dimensions of array Answers according to the number of values of Array FilmLenghts and add 2nd column or dimension
    ReDim Answers(1 To Dimension1, 1 To 2)
    ' Loop over the FilmLengths and return Answers as number of hours and remainder in minutes
    For Counter = 1 To Dimension1
            Answers(Counter, 1) = Int(FilmLengths(Counter, 1) / 60)
            Answers(Counter, 2) = FilmLengths(Counter, 1) Mod 60
    Next Counter
    ' Populate columns F and G with the Answers
    Range("f3", Range("f3").Offset(Counter - 1, 1)) = Answers
    Erase FilmLengths
    Erase Answers

End Sub


Thank you and regards,



Head office

Kingsmoor House

Railway Street


SK13 2AA


Landmark Offices

99 Bishopsgate




Holiday Inn

25 Aytoun Street


M1 3AE

© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.

End of small page here
Please be aware that our website uses cookies!
I'm OK with this Tell me more ...