VBA How to add a loop with Application.WorksheetFunction.?

Question

can you please advise how to loop my code through all populated rows (based on row D)? I need to subtract d2 from ad2, d3 from ad3 and so on and put the results in ae column (offset I guess).

Ideally, avoiding entering formulas in ae and instead using Application.WorksheetFunction.Value=Total?

Sub valuedifference()
Dim Total As Double
Dim TimeX As Date
Dim TimeY As Date

With ThisWorkbook.Sheets("Test1")
TimeX = CDate(Range("d2").Value)
TimeY = CDate(Range("ad2").Value)
Total = TimeValue(TimeY) - TimeValue(TimeX)

Range("ag2").Value = Abs(Total * 24)
Range("ah2").Value = Abs(Total * 1440)
End With

End Sub

There's no `WorksheetFunction.Value`, unclear what the question has to do with `WorksheetFunction` at all. But there are many examples online of how to implement a loop. [How to find the last cell](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) is also relevant. Also you really need a `.` in front of every `Range` call. — BigBen, May 19 '21 at 20:39

score 1 · Accepted Answer · edited May 26 '21 at 08:51

1

The following macro uses Column D to find the last row, and then loops through each row and places the results in Column AE...

Sub valuedifference()

    Dim Total As Double
    Dim TimeX As Date
    Dim TimeY As Date
    Dim LastRow As Long
    Dim i As Long
    
    With ThisWorkbook.Sheets("Test1")
        LastRow = .Cells(.Rows.Count, "d").End(xlUp).Row
        For i = 2 To LastRow
            TimeX = CDate(.Range("d" & i).Value)
            TimeY = CDate(.Range("ad" & i).Value)
            Total = DateDiff("n", TimeY, TimeX)
            .Range("AE" & i).Value = Total
            .Range("AG" & i).Value = Format(Abs(Total), "#.##")
            .Range("AH" & i).Value = Format(Abs(Total), "#.##")
        Next i
    End With

End Sub

edited May 26 '21 at 08:51

Alien_Explorer

859
1
9
22

answered May 19 '21 at 20:54

Domenic

7,844
2
9
17

Superstar! Thank you so much :) – Alien_Explorer May 19 '21 at 21:06
can you advise what to do in order to avoid blank cells? If Not IsEmpty(TimeX) Then ...End If (I wanted to try with this one but it doesn't work)? – Alien_Explorer May 20 '21 at 16:25
3

You can try `If Not IsEmpty(.Range("d" & i)) And Not IsEmpty(.Range("ad" & i)) Then` – Stringeater May 20 '21 at 16:30
Interestingly, I have noticed that the code doesn't recognize the change in a day between two dates. So, e.g. if date is 23/05/21 12:00 and we subtract 24/05/21 13:00 from it it only shows the difference of 1 hour and not 25 hours same goes for minutes where it would be simply 60 mins and not 1500 minutes? Can you advise please? – Alien_Explorer May 23 '21 at 17:54
Got it - DateDiff("n", x, y) is the solution :) – Alien_Explorer May 23 '21 at 18:23

score 0 · Answer 2 · answered May 19 '21 at 21:40

I'd strongly recommend using Range variables and offsets rather than assembling cell name references.

Since the write back to the spreadsheet is a block of 3 cells, you can use an Array of the required values writing to the block to reduce spreadsheet updates.

One outstanding question for me is whether you want to capture the difference in days also, or just the time difference (regardless of date) as you do here?

Sub valuedifference()

Dim Total As Double
Dim TimeX As Date
Dim TimeY As Date
Dim LastD As Range
Dim DRange As Range
Dim ACell As Range

Set LastD = Sheets("Test1").Cells(Sheets("Test1").Cells.Rows.Count, 4).End(xlUp)
Set DRange = Range(Sheets("Test1").Range("D2"), LastD)
For Each ACell In DRange
    TimeX = CDate(ACell.Value)               ' from D column
    TimeY = CDate(ACell.Offset(0, 26).Value) ' from AD column
    Total = TimeValue(TimeY) - TimeValue(TimeX)
    ' Place results in AE rightward cells
    ACell.Offset(0, 27).Resize(1, 3).Value _
          = Array(Total, Abs(Total * 24), Abs(Total * 1440))
Next ACell

End Sub

Hi Joffan, your work is much appreciated. I will have a look now and see how it works. Thanks. PS. As of this moment, I just use the time difference. — Alien_Explorer, May 20 '21 at 08:18

VBA How to add a loop with Application.WorksheetFunction.?

2 Answers2

Linked