How to nest long formula in VBA?

[sinivasan]

Hello Everyone!
How to nest the long excel formula in vba using WorksheetFunction?

Here is my goal...
I need to convert the following into Minutes

Column A - Column B(mins)
0Day(s), 5Hour(s), 25Min(s) - 325
4Day(s), 7Hour(s), 11Min(s) - 6191
7Day(s), 8Hour(s), 2Min(s) - 10562

So I have created a following nested formula to get the result

=(LEFT(A1,(FIND("D",A1,1))-1)*24*60)+(MID(A1,(FIND(" ",A1,1))+1,(FIND("H",A1,1))-(FIND(" ",A1,1)+1))*60)+MID(A1,(FIND(",",(SUBSTITUTE(A1,","," ",1)))+2),(FIND("M",A1,1))-(FIND(",",(SUBSTITUTE(A1,","," ",1)))+2))

now I want to perform the same using VBA WorksheetFunction,

Is that possible?

Thanks in advance.

 

[Andrew Poulsom]

Welcome to Mr Excel?

Why don't you put the formula in column B convert to a value?

 

[sinivasan]

You're right!

But I want to create a macro that converts the value of column A to mins without leaving the formula in column B.

And I also want to know how to nest in VBA.

Thanks for quick response...

 

[Andrew Poulsom]

Example:

Sub Test()
    Dim WF As WorksheetFunction
    Set WF = WorksheetFunction
    With ActiveSheet.Range("A1")
        .Value = (Val(Left(.Value, WF.Find("D", .Value, 1) - 1)) * 24 * 60) + (Val(Mid(.Value, WF.Find(" ", .Value, 1) + 1, WF.Find("H", .Value, 1) - WF.Find(" ", .Value, 1) + 1)) * 60) + (Val(Mid(.Value, WF.Find(",", WF.Substitute(.Value, ",", " ", 1)) + 2, WF.Find("M", .Value, 1) - WF.Find(",", WF.Substitute(.Value, ",", " ", 1)) + 2)))
    End With
End Sub

 

[sinivasan]

It's working.
Thank you Andrew Poulsom.

 

[Jonmo1]

Just for fun, here's a shorter formula to accomplish the task

=(LEFT(A1,FIND("D",A1)-1)+TIME(MID(A1,FIND("H",A1)-2,2),MID(A1,FIND("M",A1)-2,2),0))*1440


If I were inclined to do it in VBA, it would be something like

Sub test()
Dim dys As Integer, hrs As Integer, mns As Integer
With Range("A1")
    dys = Left(.Value, InStr(1, .Value, "D") - 1)
    hrs = Mid(.Value, InStr(1, .Value, "H") - 2, 2)
    mns = Mid(.Value, InStr(1, .Value, "M") - 2, 2)
    .Value = (dys + TimeSerial(hrs, mns, 0)) * 1440
End With
End Sub

 

[sinivasan]

Can I use my own variable instead of .Value like

MyVariable = ActiveCell.Value

 

[Andrew Poulsom]

Inside the With ... End With loop it would be:

MyVariable = .Value