Jump to content
PLC Forum


Sign in to follow this  
trittico6969

Trasformare U Codice Vba In Formula Excel 2003

Recommended Posts

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

Share this post


Link to post
Share on other sites

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)………………..

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...