Trasformare U Codice Vba In Formula Excel 2003

[trittico6969]

È possibile trasformare questo codice in una semplice formula

Sub minuti()
Dim AG As Range
Dim AH As Range
Dim AI As Range
Dim F As Range
Dim G As Range
Dim H As Range
Dim vero As Boolean

Set AG = ThisWorkbook.ActiveSheet.Range("AG31")
Set AH = ThisWorkbook.ActiveSheet.Range("AH31")
Set AI = ThisWorkbook.ActiveSheet.Range("AI31")
Set F = ThisWorkbook.ActiveSheet.Range("F10")
Set G = ThisWorkbook.ActiveSheet.Range("G10")
Set H = ThisWorkbook.ActiveSheet.Range("H10")

vero = False
If ActiveSheet.Name = "RIEP" Then vero = True
If Not vero Then

Select Case True

'1° CASO AGGIUNTO
'TUTTE E 3 LE CELLE MINORI O UGUALI A 30
Case VBA.Minute(AG) <= 30 And VBA.Minute(AH) <= 30 And VBA.Minute(AI) <= 30
   Application.EnableEvents = False
   If AG > 1 Then F = VBA.Int((AG) * 24) Else F = VBA.Hour(AG)
   If AH > 1 Then G = VBA.Int((AH) * 24) Else G = VBA.Hour(AH)
   If AI > 1 Then H = VBA.Int((AI) * 24) Else H = VBA.Hour(AI)
'--------------------------------------------------------------------------------
'2° CASO AGGIUNTO
'1 CELLA su 3 MINORE O UGUALE A 30 (le altre 2 uguali a 0)
Case VBA.Minute(AG) <= 30 And VBA.Minute(AH) = 0 And VBA.Minute(AI) = 0
   Application.EnableEvents = False
   If AG > 1 Then F = VBA.Int((AG) * 24) Else F = VBA.Hour(AG)
   If AH > 1 Then G = VBA.Int((AH) * 24) Else G = VBA.Hour(AH)
   If AI > 1 Then H = VBA.Int((AI) * 24) Else H = VBA.Hour(AI)

Case VBA.Minute(AG) = 0 And VBA.Minute(AH) <= 30 And VBA.Minute(AI) = 0
   Application.EnableEvents = False
   If AG > 1 Then F = VBA.Int((AG) * 24) Else F = VBA.Hour(AG)
   If AH > 1 Then G = VBA.Int((AH) * 24) Else G = VBA.Hour(AH)
   If AI > 1 Then H = VBA.Int((AI) * 24) Else H = VBA.Hour(AI)

Case VBA.Minute(AG) = 0 And VBA.Minute(AH) = 0 And VBA.Minute(AI) <= 30
   Application.EnableEvents = False
   If AG > 1 Then F = VBA.Int((AG) * 24) Else F = VBA.Hour(AG)
   If AH > 1 Then G = VBA.Int((AH) * 24) Else G = VBA.Hour(AH)
   If AI > 1 Then H = VBA.Int((AI) * 24) Else H = VBA.Hour(AI)
'--------------------------------------------------------------------------------------
'3° CASO AGGIUNTO
'2 CELLE SU 3 MINORI O UGUALI A 30 (l'altra uguale a 0)
Case VBA.Minute(AG) <= 30 And VBA.Minute(AH) <= 30 And VBA.Minute(AI) = 0
   Application.EnableEvents = False
   If AG > 1 Then F = VBA.Int((AG) * 24) Else F = VBA.Hour(AG)
   If AH > 1 Then G = VBA.Int((AH) * 24) Else G = VBA.Hour(AH)
   If AI > 1 Then H = VBA.Int((AI) * 24) Else H = VBA.Hour(AI)

Case VBA.Minute(AG) <= 30 And VBA.Minute(AH) = 0 And VBA.Minute(AI) <= 30
   Application.EnableEvents = False
   If AG > 1 Then F = VBA.Int((AG) * 24) Else F = VBA.Hour(AG)
   If AH > 1 Then G = VBA.Int((AH) * 24) Else G = VBA.Hour(AH)
   If AI > 1 Then H = VBA.Int((AI) * 24) Else H = VBA.Hour(AI)

Case VBA.Minute(AG) = 0 And VBA.Minute(AH) <= 30 And VBA.Minute(AI) <= 30
   Application.EnableEvents = False
   If AG > 1 Then F = VBA.Int((AG) * 24) Else F = VBA.Hour(AG)
   If AH > 1 Then G = VBA.Int((AH) * 24) Else G = VBA.Hour(AH)
   If AI > 1 Then H = VBA.Int((AI) * 24) Else H = VBA.Hour(AI)
'------------------------------------------------------------------------------------------

'PRIMO CASO
Case VBA.Minute(AG) > 30 And VBA.Minute(AH) = 0 And VBA.Minute(AI) = 0
   Application.EnableEvents = False
   If AG > 1 Then F = VBA.Int((AG) * 24) + 1 Else F = VBA.Hour(AG) + 1
   If AH > 1 Then G = VBA.Int((AH) * 24) Else G = VBA.Hour(AH)
   If AI > 1 Then H = VBA.Int((AI) * 24) Else H = VBA.Hour(AI)

Case VBA.Minute(AG) = 0 And VBA.Minute(AH) > 30 And VBA.Minute(AI) = 0
   Application.EnableEvents = False
   If AG > 1 Then F = VBA.Int((AG) * 24) Else F = VBA.Hour(AG)
   If AH > 1 Then G = VBA.Int((AH) * 24) + 1 Else G = VBA.Hour(AH) + 1
   If AI > 1 Then H = VBA.Int((AI) * 24) Else H = VBA.Hour(AI)

Case VBA.Minute(AG) = 0 And VBA.Minute(AH) = 0 And VBA.Minute(AI) > 30
   Application.EnableEvents = False
   If AG > 1 Then F = VBA.Int((AG) * 24) Else F = VBA.Hour(AG)
   If AH > 1 Then G = VBA.Int((AH) * 24) Else G = VBA.Hour(AH)
   If AI > 1 Then H = VBA.Int((AI) * 24) + 1 Else H = VBA.Hour(AI) + 1
'---------------------------------------------------------------------------------

'SECONDO CASO
Case VBA.Minute(AG) > 0 And VBA.Minute(AH) > 0 And VBA.Minute(AI) = 0 _
   And VBA.Minute(AG) + VBA.Minute(AH) > 30 And VBA.Minute(AG) _
   + VBA.Minute(AH) <= 60
   Application.EnableEvents = False
   If AG > 1 Then F = VBA.Int((AG) * 24) + 1 Else F = VBA.Hour(AG) + 1
   If AH > 1 Then G = VBA.Int((AH) * 24) Else G = VBA.Hour(AH)
   If AI > 1 Then H = VBA.Int((AI) * 24) Else H = VBA.Hour(AI)
    
Case VBA.Minute(AG) > 0 And VBA.Minute(AH) > 0 And VBA.Minute(AI) = 0 _
   And VBA.Minute(AG) + VBA.Minute(AH) > 60
   Application.EnableEvents = False
   If AG > 1 Then F = VBA.Int((AG) * 24) Else F = VBA.Hour(AG)
   If AH > 1 Then G = VBA.Int((AH) * 24) + 1 Else G = VBA.Hour(AH) + 1
   If AI > 1 Then H = VBA.Int((AI) * 24) Else H = VBA.Hour(AI)
'----------------------------------------------------------------------------------

'TERZO CASO
Case VBA.Minute(AG) > 0 And VBA.Minute(AH) = 0 And VBA.Minute(AI) > 0 _
   And VBA.Minute(AG) + VBA.Minute(AI) > 30 And VBA.Minute(AG) _
   + VBA.Minute(AI) <= 60
   Application.EnableEvents = False
   If AG > 1 Then F = VBA.Int((AG) * 24) Else F = VBA.Hour(AG)
   If AH > 1 Then G = VBA.Int((AH) * 24) + 1 Else G = VBA.Hour(AH) + 1
   If AI > 1 Then H = VBA.Int((AI) * 24) Else H = VBA.Hour(AI)

Case VBA.Minute(AG) > 0 And VBA.Minute(AH) = 0 And VBA.Minute(AI) > 0 _
   And VBA.Minute(AG) + VBA.Minute(AI) > 60
   Application.EnableEvents = False
   If AG > 1 Then F = VBA.Int((AG) * 24) Else F = VBA.Hour(AG)
   If AH > 1 Then G = VBA.Int((AH) * 24) Else G = VBA.Hour(AH)
   If AI > 1 Then H = VBA.Int((AI) * 24) + 1 Else H = VBA.Hour(AI) + 1
'-------------------------------------------------------------------------------------

'QUARTO CASO
Case VBA.Minute(AG) = 0 And VBA.Minute(AH) > 0 And VBA.Minute(AI) > 0 _
   And VBA.Minute(AH) + VBA.Minute(AI) > 30 And VBA.Minute(AH) _
   + VBA.Minute(AI) <= 60
   Application.EnableEvents = False
   If AG > 1 Then F = VBA.Int((AG) * 24) Else F = VBA.Hour(AG)
   If AH > 1 Then G = VBA.Int((AH) * 24) + 1 Else G = VBA.Hour(AH) + 1
   If AI > 1 Then H = VBA.Int((AI) * 24) Else H = VBA.Hour(AI)

Case VBA.Minute(AG) = 0 And VBA.Minute(AH) > 0 And VBA.Minute(AI) > 0 _
   And VBA.Minute(AH) + VBA.Minute(AI) > 60
   Application.EnableEvents = False
   If AG > 1 Then F = VBA.Int((AG) * 24) Else F = VBA.Hour(AG)
   If AH > 1 Then G = VBA.Int((AH) * 24) Else G = VBA.Hour(AH)
   If AI > 1 Then H = VBA.Int((AI) * 24) + 1 Else H = VBA.Hour(AI) + 1
'-----------------------------------------------------------------------------------------

'OTTAVO CASO
Case VBA.Minute(AG) > 0 And VBA.Minute(AH) > 0 And VBA.Minute(AI) > 0 _
   And VBA.Minute(AG) + VBA.Minute(AH) + VBA.Minute(AI) > 150
   Application.EnableEvents = False
   If AG > 1 Then F = VBA.Int((AG) * 24) Else F = VBA.Hour(AG)
   If AH > 1 Then G = VBA.Int((AH) * 24) + 1 Else G = VBA.Hour(AH) + 1
   If AI > 1 Then H = VBA.Int((AI) * 24) + 2 Else H = VBA.Hour(AI) + 2
'------------------------------------------------------------------------------------------

'SETTIMO CASO
Case VBA.Minute(AG) > 0 And VBA.Minute(AH) > 0 And VBA.Minute(AI) > 0 _
   And VBA.Minute(AG) + VBA.Minute(AH) + VBA.Minute(AI) > 120 _
   And VBA.Minute(AG) + VBA.Minute(AH) + VBA.Minute(AI) <= 150
   Application.EnableEvents = False
   If AG > 1 Then F = VBA.Int((AG) * 24) Else F = VBA.Hour(AG)
   If AH > 1 Then G = VBA.Int((AH) * 24) Else G = VBA.Hour(AH)
   If AI > 1 Then H = VBA.Int((AI) * 24) + 2 Else H = VBA.Hour(AI) + 2
'--------------------------------------------------------------------------------------------

'SESTO CASO
Case VBA.Minute(AG) > 0 And VBA.Minute(AH) > 0 And VBA.Minute(AI) > 0 _
   And VBA.Minute(AG) + VBA.Minute(AH) + VBA.Minute(AI) > 60 _
   And VBA.Minute(AG) + VBA.Minute(AH) + VBA.Minute(AI) <= 90
   Application.EnableEvents = False
   If AG > 1 Then F = VBA.Int((AG) * 24) Else F = VBA.Hour(AG)
   If AH > 1 Then G = VBA.Int((AH) * 24) Else G = VBA.Hour(AH)
   If AI > 1 Then H = VBA.Int((AI) * 24) + 1 Else H = VBA.Hour(AI) + 1

Case VBA.Minute(AG) > 0 And VBA.Minute(AH) > 0 And VBA.Minute(AI) > 0 _
   And VBA.Minute(AG) + VBA.Minute(AH) + VBA.Minute(AI) > 90 _
   And VBA.Minute(AG) + VBA.Minute(AH) + VBA.Minute(AI) <= 120
   Application.EnableEvents = False
   If AG > 1 Then F = VBA.Int((AG) * 24) Else F = VBA.Hour(AG)
   If AH > 1 Then G = VBA.Int((AH) * 24) + 1 Else G = VBA.Hour(AH) + 1
   If AI > 1 Then H = VBA.Int((AI) * 24) + 1 Else H = VBA.Hour(AI) + 1
'-----------------------------------------------------------------------------------------

'QUINTO CASO
Case VBA.Minute(AG) > 0 And VBA.Minute(AH) > 0 And VBA.Minute(AI) > 0 _
   And VBA.Minute(AG) + VBA.Minute(AH) + VBA.Minute(AI) > 30 _
   And VBA.Minute(AG) + VBA.Minute(AH) + VBA.Minute(AI) <= 60
   Application.EnableEvents = False
   If AG > 1 Then F = VBA.Int((AG) * 24) Else F = VBA.Hour(AG)
   If AH > 1 Then G = VBA.Int((AH) * 24) + 1 Else G = VBA.Hour(AH) + 1
   If AI > 1 Then H = VBA.Int((AI) * 24) Else H = VBA.Hour(AI)

Case VBA.Minute(AG) > 0 And VBA.Minute(AH) > 0 And VBA.Minute(AI) > 0 _
   And VBA.Minute(AG) + VBA.Minute(AH) + VBA.Minute(AI) > 60
   Application.EnableEvents = False
   If AG > 1 Then F = VBA.Int((AG) * 24) Else F = VBA.Hour(AG)
   If AH > 1 Then G = VBA.Int((AH) * 24) Else G = VBA.Hour(AH)
   If AI > 1 Then H = VBA.Int((AI) * 24) + 1 Else H = VBA.Hour(AI) + 1
'--------------------------------------------------------------------------------------------


'---------------------------------------------------------------------------------------------
End Select
End If
Set AG = Nothing
Set AH = Nothing
Set AI = Nothing
Set F = Nothing
Set G = Nothing
Set H = Nothing
Application.EnableEvents = True
End Sub

[trittico6969]

Non so quale dei due esempi è corretto

Pimo caso

 =SE(E(MINUTO(AG31)<=30;MINUTO(AH31)<=30;MINUTO(AI31)<=30);SE(AG31>1;INT(AG31*24);ORA(AG31));0) +

Secondo caso

SE(E(MINUTO(AG31)<=30;MINUTO(AH31)=0;MINUTO(AI31)=0);SE(AG31>1;INT(AG31*24);ORA(AG31));0)……

oppure primo caso

=SE(E(MINUTO(AG31)<=30;MINUTO(AH31)<=30;MINUTO(AI31)<=30);SE(AG31>1;INT(AG31*24);ORA(AG31));0) +
SE(E(MINUTO(AG31)<=30;MINUTO(AH31)<=30;MINUTO(AI31)<=30);SE(AG31>1;INT(AH31*24);ORA(AH31));0) +
SE(E(MINUTO(AG31)<=30;MINUTO(AH31)<=30;MINUTO(AI31)<=30);SE(AG31>1;INT(AI31*24);ORA(AI31));0) +

Secondo caso

SE(E(MINUTO(AG31)<=30;MINUTO(AH31)=0;MINUTO(AI31)=0);SE(AG31>1;INT(AG31*24);ORA(AG31));0)+
SE(E(MINUTO(AG31)<=30;MINUTO(AH31)=0;MINUTO(AI31)=0);SE(AG31>1;INT(AH31*24);ORA(AH31));0)+
SE(E(MINUTO(AG31)<=30;MINUTO(AH31)=0;MINUTO(AI31)=0);SE(AG31>1;INT(AI31*24);ORA(AI31));0)………………..