اذهب الي المحتوي
أوفيسنا

إختصار كود vba

لزهر مدلل

بواسطه لزهر مدلل
في منتدى الاكسيل Excel

 مشاركة
إذهب إلى أفضل إجابة Solved by محي الدين ابو البشر,

الردود الموصى بها

لزهر مدلل

لزهر مدلل

قام بنشر
قام بنشر

السلام عليكم

قمت بتحويل معادلات إلى كود vba لكنه طويل بعض الشيئ فإذا أمكن إختصاره

بارك الله فيكم

Private Sub Workbook_Open()

Range("U2") = Evaluate("=EOMONTH(TODAY(),-2)+1")
Range("V2") = Evaluate("=DAY(DATE(YEAR($U$2),MONTH($U$2)+1,0))")
Range("J5") = Evaluate("=UPPER(TEXT(U2,""[$-40c] mmmm yyyy""))")
Range("M12") = Evaluate("=IF(1<=$V$2,IF(OR(TEXT($U$2,""DDDD"")=""friday"",TEXT($U$2,""DDDD"")=""saturday""),0,1),"""")")
Range("N12") = Evaluate("=IF(1<=$V$2,IF(OR(TEXT($U$2,""DDDD"")=""friday"",TEXT($U$2,""DDDD"")=""saturday""),0,1),"""")")
Range("M13") = Evaluate("=IF(2<=$V$2,IF(OR(TEXT($U$2+1,""DDDD"")=""friday"",TEXT($U$2+1,""DDDD"")=""saturday""),0,1),"""")")
Range("N13") = Evaluate("=IF(2<=$V$2,IF(OR(TEXT($U$2+1,""DDDD"")=""friday"",TEXT($U$2+1,""DDDD"")=""saturday""),0,1),"""")")
Range("M14") = Evaluate("=IF(3<=$V$2,IF(OR(TEXT($U$2+2,""DDDD"")=""friday"",TEXT($U$2+2,""DDDD"")=""saturday""),0,1),"""")")
Range("N14") = Evaluate("=IF(3<=$V$2,IF(OR(TEXT($U$2+2,""DDDD"")=""friday"",TEXT($U$2+2,""DDDD"")=""saturday""),0,1),"""")")
Range("M15") = Evaluate("=IF(4<=$V$2,IF(OR(TEXT($U$2+3,""DDDD"")=""friday"",TEXT($U$2+3,""DDDD"")=""saturday""),0,1),"""")")
Range("N15") = Evaluate("=IF(4<=$V$2,IF(OR(TEXT($U$2+3,""DDDD"")=""friday"",TEXT($U$2+3,""DDDD"")=""saturday""),0,1),"""")")
Range("M16") = Evaluate("=IF(5<=$V$2,IF(OR(TEXT($U$2+4,""DDDD"")=""friday"",TEXT($U$2+4,""DDDD"")=""saturday""),0,1),"""")")
Range("N16") = Evaluate("=IF(5<=$V$2,IF(OR(TEXT($U$2+4,""DDDD"")=""friday"",TEXT($U$2+4,""DDDD"")=""saturday""),0,1),"""")")
Range("M17") = Evaluate("=IF(6<=$V$2,IF(OR(TEXT($U$2+5,""DDDD"")=""friday"",TEXT($U$2+5,""DDDD"")=""saturday""),0,1),"""")")
Range("N17") = Evaluate("=IF(6<=$V$2,IF(OR(TEXT($U$2+5,""DDDD"")=""friday"",TEXT($U$2+5,""DDDD"")=""saturday""),0,1),"""")")
Range("M18") = Evaluate("=IF(7<=$V$2,IF(OR(TEXT($U$2+6,""DDDD"")=""friday"",TEXT($U$2+6,""DDDD"")=""saturday""),0,1),"""")")
Range("N18") = Evaluate("=IF(7<=$V$2,IF(OR(TEXT($U$2+6,""DDDD"")=""friday"",TEXT($U$2+6,""DDDD"")=""saturday""),0,1),"""")")
Range("M19") = Evaluate("=IF(8<=$V$2,IF(OR(TEXT($U$2+7,""DDDD"")=""friday"",TEXT($U$2+7,""DDDD"")=""saturday""),0,1),"""")")
Range("N19") = Evaluate("=IF(8<=$V$2,IF(OR(TEXT($U$2+7,""DDDD"")=""friday"",TEXT($U$2+7,""DDDD"")=""saturday""),0,1),"""")")
Range("M20") = Evaluate("=IF(9<=$V$2,IF(OR(TEXT($U$2+8,""DDDD"")=""friday"",TEXT($U$2+8,""DDDD"")=""saturday""),0,1),"""")")
Range("N20") = Evaluate("=IF(9<=$V$2,IF(OR(TEXT($U$2+8,""DDDD"")=""friday"",TEXT($U$2+8,""DDDD"")=""saturday""),0,1),"""")")
Range("M21") = Evaluate("=IF(10<=$V$2,IF(OR(TEXT($U$2+9,""DDDD"")=""friday"",TEXT($U$2+9,""DDDD"")=""saturday""),0,1),"""")")
Range("N21") = Evaluate("=IF(10<=$V$2,IF(OR(TEXT($U$2+9,""DDDD"")=""friday"",TEXT($U$2+9,""DDDD"")=""saturday""),0,1),"""")")
Range("M22") = Evaluate("=IF(11<=$V$2,IF(OR(TEXT($U$2+10,""DDDD"")=""friday"",TEXT($U$2+10,""DDDD"")=""saturday""),0,1),"""")")
Range("N22") = Evaluate("=IF(11<=$V$2,IF(OR(TEXT($U$2+10,""DDDD"")=""friday"",TEXT($U$2+10,""DDDD"")=""saturday""),0,1),"""")")
Range("M23") = Evaluate("=IF(12<=$V$2,IF(OR(TEXT($U$2+11,""DDDD"")=""friday"",TEXT($U$2+11,""DDDD"")=""saturday""),0,1),"""")")
Range("N23") = Evaluate("=IF(12<=$V$2,IF(OR(TEXT($U$2+11,""DDDD"")=""friday"",TEXT($U$2+11,""DDDD"")=""saturday""),0,1),"""")")
Range("M24") = Evaluate("=IF(13<=$V$2,IF(OR(TEXT($U$2+12,""DDDD"")=""friday"",TEXT($U$2+12,""DDDD"")=""saturday""),0,1),"""")")
Range("N24") = Evaluate("=IF(13<=$V$2,IF(OR(TEXT($U$2+12,""DDDD"")=""friday"",TEXT($U$2+12,""DDDD"")=""saturday""),0,1),"""")")
Range("M25") = Evaluate("=IF(14<=$V$2,IF(OR(TEXT($U$2+13,""DDDD"")=""friday"",TEXT($U$2+13,""DDDD"")=""saturday""),0,1),"""")")
Range("N25") = Evaluate("=IF(14<=$V$2,IF(OR(TEXT($U$2+13,""DDDD"")=""friday"",TEXT($U$2+13,""DDDD"")=""saturday""),0,1),"""")")
Range("M26") = Evaluate("=IF(15<=$V$2,IF(OR(TEXT($U$2+14,""DDDD"")=""friday"",TEXT($U$2+14,""DDDD"")=""saturday""),0,1),"""")")
Range("N26") = Evaluate("=IF(15<=$V$2,IF(OR(TEXT($U$2+14,""DDDD"")=""friday"",TEXT($U$2+14,""DDDD"")=""saturday""),0,1),"""")")
Range("M27") = Evaluate("=IF(16<=$V$2,IF(OR(TEXT($U$2+15,""DDDD"")=""friday"",TEXT($U$2+15,""DDDD"")=""saturday""),0,1),"""")")
Range("N27") = Evaluate("=IF(16<=$V$2,IF(OR(TEXT($U$2+15,""DDDD"")=""friday"",TEXT($U$2+15,""DDDD"")=""saturday""),0,1),"""")")
Range("M28") = Evaluate("=IF(17<=$V$2,IF(OR(TEXT($U$2+16,""DDDD"")=""friday"",TEXT($U$2+16,""DDDD"")=""saturday""),0,1),"""")")
Range("N28") = Evaluate("=IF(17<=$V$2,IF(OR(TEXT($U$2+16,""DDDD"")=""friday"",TEXT($U$2+16,""DDDD"")=""saturday""),0,1),"""")")
Range("M29") = Evaluate("=IF(18<=$V$2,IF(OR(TEXT($U$2+17,""DDDD"")=""friday"",TEXT($U$2+17,""DDDD"")=""saturday""),0,1),"""")")
Range("N29") = Evaluate("=IF(18<=$V$2,IF(OR(TEXT($U$2+17,""DDDD"")=""friday"",TEXT($U$2+17,""DDDD"")=""saturday""),0,1),"""")")
Range("M30") = Evaluate("=IF(19<=$V$2,IF(OR(TEXT($U$2+18,""DDDD"")=""friday"",TEXT($U$2+18,""DDDD"")=""saturday""),0,1),"""")")
Range("N30") = Evaluate("=IF(19<=$V$2,IF(OR(TEXT($U$2+18,""DDDD"")=""friday"",TEXT($U$2+18,""DDDD"")=""saturday""),0,1),"""")")
Range("M31") = Evaluate("=IF(20<=$V$2,IF(OR(TEXT($U$2+19,""DDDD"")=""friday"",TEXT($U$2+19,""DDDD"")=""saturday""),0,1),"""")")
Range("N31") = Evaluate("=IF(20<=$V$2,IF(OR(TEXT($U$2+19,""DDDD"")=""friday"",TEXT($U$2+19,""DDDD"")=""saturday""),0,1),"""")")
Range("M32") = Evaluate("=IF(21<=$V$2,IF(OR(TEXT($U$2+20,""DDDD"")=""friday"",TEXT($U$2+20,""DDDD"")=""saturday""),0,1),"""")")
Range("N32") = Evaluate("=IF(21<=$V$2,IF(OR(TEXT($U$2+20,""DDDD"")=""friday"",TEXT($U$2+20,""DDDD"")=""saturday""),0,1),"""")")
Range("M33") = Evaluate("=IF(22<=$V$2,IF(OR(TEXT($U$2+21,""DDDD"")=""friday"",TEXT($U$2+21,""DDDD"")=""saturday""),0,1),"""")")
Range("N33") = Evaluate("=IF(22<=$V$2,IF(OR(TEXT($U$2+21,""DDDD"")=""friday"",TEXT($U$2+21,""DDDD"")=""saturday""),0,1),"""")")
Range("M34") = Evaluate("=IF(23<=$V$2,IF(OR(TEXT($U$2+22,""DDDD"")=""friday"",TEXT($U$2+22,""DDDD"")=""saturday""),0,1),"""")")
Range("N34") = Evaluate("=IF(23<=$V$2,IF(OR(TEXT($U$2+22,""DDDD"")=""friday"",TEXT($U$2+22,""DDDD"")=""saturday""),0,1),"""")")
Range("M35") = Evaluate("=IF(24<=$V$2,IF(OR(TEXT($U$2+23,""DDDD"")=""friday"",TEXT($U$2+23,""DDDD"")=""saturday""),0,1),"""")")
Range("N35") = Evaluate("=IF(24<=$V$2,IF(OR(TEXT($U$2+23,""DDDD"")=""friday"",TEXT($U$2+23,""DDDD"")=""saturday""),0,1),"""")")
Range("M36") = Evaluate("=IF(25<=$V$2,IF(OR(TEXT($U$2+24,""DDDD"")=""friday"",TEXT($U$2+24,""DDDD"")=""saturday""),0,1),"""")")
Range("N36") = Evaluate("=IF(25<=$V$2,IF(OR(TEXT($U$2+24,""DDDD"")=""friday"",TEXT($U$2+24,""DDDD"")=""saturday""),0,1),"""")")
Range("M37") = Evaluate("=IF(26<=$V$2,IF(OR(TEXT($U$2+25,""DDDD"")=""friday"",TEXT($U$2+25,""DDDD"")=""saturday""),0,1),"""")")
Range("N37") = Evaluate("=IF(26<=$V$2,IF(OR(TEXT($U$2+25,""DDDD"")=""friday"",TEXT($U$2+25,""DDDD"")=""saturday""),0,1),"""")")
Range("M38") = Evaluate("=IF(27<=$V$2,IF(OR(TEXT($U$2+26,""DDDD"")=""friday"",TEXT($U$2+26,""DDDD"")=""saturday""),0,1),"""")")
Range("N38") = Evaluate("=IF(27<=$V$2,IF(OR(TEXT($U$2+26,""DDDD"")=""friday"",TEXT($U$2+26,""DDDD"")=""saturday""),0,1),"""")")
Range("M39") = Evaluate("=IF(28<=$V$2,IF(OR(TEXT($U$2+27,""DDDD"")=""friday"",TEXT($U$2+27,""DDDD"")=""saturday""),0,1),"""")")
Range("N39") = Evaluate("=IF(28<=$V$2,IF(OR(TEXT($U$2+27,""DDDD"")=""friday"",TEXT($U$2+27,""DDDD"")=""saturday""),0,1),"""")")
Range("M40") = Evaluate("=IF(29<=$V$2,IF(OR(TEXT($U$2+28,""DDDD"")=""friday"",TEXT($U$2+28,""DDDD"")=""saturday""),0,1),"""")")
Range("N40") = Evaluate("=IF(29<=$V$2,IF(OR(TEXT($U$2+28,""DDDD"")=""friday"",TEXT($U$2+28,""DDDD"")=""saturday""),0,1),"""")")
Range("M41") = Evaluate("=IF(30<=$V$2,IF(OR(TEXT($U$2+29,""DDDD"")=""friday"",TEXT($U$2+29,""DDDD"")=""saturday""),0,1),"""")")
Range("N41") = Evaluate("=IF(30<=$V$2,IF(OR(TEXT($U$2+29,""DDDD"")=""friday"",TEXT($U$2+29,""DDDD"")=""saturday""),0,1),"""")")
Range("M42") = Evaluate("=IF(31<=$V$2,IF(OR(TEXT($U$2+30,""DDDD"")=""friday"",TEXT($U$2+30,""DDDD"")=""saturday""),0,1),"""")")
Range("N42") = Evaluate("=IF(31<=$V$2,IF(OR(TEXT($U$2+30,""DDDD"")=""friday"",TEXT($U$2+30,""DDDD"")=""saturday""),0,1),"""")")
Range("C12") = Evaluate("=IF(M12=1,""08H00"","""")")
Range("C13") = Evaluate("=IF(M13=1,""08H00"","""")")
Range("C14") = Evaluate("=IF(M14=1,""08H00"","""")")
Range("C15") = Evaluate("=IF(M15=1,""08H00"","""")")
Range("C16") = Evaluate("=IF(M16=1,""08H00"","""")")
Range("C17") = Evaluate("=IF(M17=1,""08H00"","""")")
Range("C18") = Evaluate("=IF(M18=1,""08H00"","""")")
Range("C19") = Evaluate("=IF(M19=1,""08H00"","""")")
Range("C20") = Evaluate("=IF(M20=1,""08H00"","""")")
Range("C21") = Evaluate("=IF(M21=1,""08H00"","""")")
Range("C22") = Evaluate("=IF(M22=1,""08H00"","""")")
Range("C23") = Evaluate("=IF(M23=1,""08H00"","""")")
Range("C24") = Evaluate("=IF(M24=1,""08H00"","""")")
Range("C25") = Evaluate("=IF(M25=1,""08H00"","""")")
Range("C26") = Evaluate("=IF(M26=1,""08H00"","""")")
Range("C27") = Evaluate("=IF(M27=1,""08H00"","""")")
Range("C28") = Evaluate("=IF(M28=1,""08H00"","""")")
Range("C29") = Evaluate("=IF(M29=1,""08H00"","""")")
Range("C30") = Evaluate("=IF(M30=1,""08H00"","""")")
Range("C31") = Evaluate("=IF(M31=1,""08H00"","""")")
Range("C32") = Evaluate("=IF(M32=1,""08H00"","""")")
Range("C33") = Evaluate("=IF(M33=1,""08H00"","""")")
Range("C34") = Evaluate("=IF(M34=1,""08H00"","""")")
Range("C35") = Evaluate("=IF(M35=1,""08H00"","""")")
Range("C36") = Evaluate("=IF(M36=1,""08H00"","""")")
Range("C37") = Evaluate("=IF(M37=1,""08H00"","""")")
Range("C38") = Evaluate("=IF(M38=1,""08H00"","""")")
Range("C39") = Evaluate("=IF(M39=1,""08H00"","""")")
Range("C40") = Evaluate("=IF(M40=1,""08H00"","""")")
Range("C41") = Evaluate("=IF(M41=1,""08H00"","""")")
Range("C42") = Evaluate("=IF(M42=1,""08H00"","""")")
Range("F12") = Evaluate("=IF(M12=1,""16H30"","""")")
Range("F13") = Evaluate("=IF(M13=1,""16H30"","""")")
Range("F14") = Evaluate("=IF(M14=1,""16H30"","""")")
Range("F15") = Evaluate("=IF(M15=1,""16H30"","""")")
Range("F16") = Evaluate("=IF(M16=1,""16H30"","""")")
Range("F17") = Evaluate("=IF(M17=1,""16H30"","""")")
Range("F18") = Evaluate("=IF(M18=1,""16H30"","""")")
Range("F19") = Evaluate("=IF(M19=1,""16H30"","""")")
Range("F20") = Evaluate("=IF(M20=1,""16H30"","""")")
Range("F21") = Evaluate("=IF(M21=1,""16H30"","""")")
Range("F22") = Evaluate("=IF(M22=1,""16H30"","""")")
Range("F23") = Evaluate("=IF(M23=1,""16H30"","""")")
Range("F24") = Evaluate("=IF(M24=1,""16H30"","""")")
Range("F25") = Evaluate("=IF(M25=1,""16H30"","""")")
Range("F26") = Evaluate("=IF(M26=1,""16H30"","""")")
Range("F27") = Evaluate("=IF(M27=1,""16H30"","""")")
Range("F28") = Evaluate("=IF(M28=1,""16H30"","""")")
Range("F29") = Evaluate("=IF(M29=1,""16H30"","""")")
Range("F30") = Evaluate("=IF(M30=1,""16H30"","""")")
Range("F31") = Evaluate("=IF(M31=1,""16H30"","""")")
Range("F32") = Evaluate("=IF(M32=1,""16H30"","""")")
Range("F33") = Evaluate("=IF(M33=1,""16H30"","""")")
Range("F34") = Evaluate("=IF(M34=1,""16H30"","""")")
Range("F35") = Evaluate("=IF(M35=1,""16H30"","""")")
Range("F36") = Evaluate("=IF(M36=1,""16H30"","""")")
Range("F37") = Evaluate("=IF(M37=1,""16H30"","""")")
Range("F38") = Evaluate("=IF(M38=1,""16H30"","""")")
Range("F39") = Evaluate("=IF(M39=1,""16H30"","""")")
Range("F40") = Evaluate("=IF(M40=1,""16H30"","""")")
Range("F41") = Evaluate("=IF(M41=1,""16H30"","""")")
Range("F42") = Evaluate("=IF(M42=1,""16H30"","""")")
Range("E12") = Evaluate("=IF(N12=0,""R.H"","""")")
Range("E13") = Evaluate("=IF(M13=0,""R.H"","""")")
Range("E14") = Evaluate("=IF(M14=0,""R.H"","""")")
Range("E15") = Evaluate("=IF(M15=0,""R.H"","""")")
Range("E16") = Evaluate("=IF(M16=0,""R.H"","""")")
Range("E17") = Evaluate("=IF(M17=0,""R.H"","""")")
Range("E18") = Evaluate("=IF(M18=0,""R.H"","""")")
Range("E19") = Evaluate("=IF(M19=0,""R.H"","""")")
Range("E20") = Evaluate("=IF(M20=0,""R.H"","""")")
Range("E21") = Evaluate("=IF(M21=0,""R.H"","""")")
Range("E22") = Evaluate("=IF(M22=0,""R.H"","""")")
Range("E23") = Evaluate("=IF(M23=0,""R.H"","""")")
Range("E24") = Evaluate("=IF(M24=0,""R.H"","""")")
Range("E25") = Evaluate("=IF(M25=0,""R.H"","""")")
Range("E26") = Evaluate("=IF(M26=0,""R.H"","""")")
Range("E27") = Evaluate("=IF(M27=0,""R.H"","""")")
Range("E28") = Evaluate("=IF(M28=0,""R.H"","""")")
Range("E29") = Evaluate("=IF(M29=0,""R.H"","""")")
Range("E30") = Evaluate("=IF(M30=0,""R.H"","""")")
Range("E31") = Evaluate("=IF(M31=0,""R.H"","""")")
Range("E32") = Evaluate("=IF(M32=0,""R.H"","""")")
Range("E33") = Evaluate("=IF(M33=0,""R.H"","""")")
Range("E34") = Evaluate("=IF(M34=0,""R.H"","""")")
Range("E35") = Evaluate("=IF(M35=0,""R.H"","""")")
Range("E36") = Evaluate("=IF(M36=0,""R.H"","""")")
Range("E37") = Evaluate("=IF(M37=0,""R.H"","""")")
Range("E38") = Evaluate("=IF(M38=0,""R.H"","""")")
Range("E39") = Evaluate("=IF(M39=0,""R.H"","""")")
Range("E40") = Evaluate("=IF(AND(A40>=29,N40=0),""R.H"","""")")
Range("E41") = Evaluate("=IF(AND(A41>=30,N41=0),""R.H"","""")")
Range("E42") = Evaluate("=IF(AND(A42>=31,N42=0),""R.H"","""")")
Range("A40") = Evaluate("=IF(V2>=29,29,"""")")
Range("A41") = Evaluate("=IF(V2>=30,30,"""")")
Range("A42") = Evaluate("=IF(V2>=31,31,"""")")

                       
End Sub

 

  • أفضل إجابة
محي الدين ابو البشر

محي الدين ابو البشر

قام بنشر
  • أفضل إجابة
قام بنشر

ربما 

Private Sub Workbook_Open()

Range("U2") = Evaluate("=EOMONTH(TODAY(),-2)+1")
Range("V2") = Evaluate("=DAY(DATE(YEAR($U$2),MONTH($U$2)+1,0))")
Range("J5") = Evaluate("=UPPER(TEXT(U2,""[$-40c] mmmm yyyy""))")
x = 0
 For i = 12 To 42
Range("M" & i) = Evaluate("=IF(1<=V" & 2 & ",IF(OR(TEXT(U" & 2 + x & ",""DDDD"")=""friday"",TEXT(U2+" & x & ",""DDDD"")=""saturday""),0,1),"""")")
Range("N" & i) = Evaluate("=IF(1<=V" & 2 & ",IF(OR(TEXT(U" & 2 + x & ",""DDDD"")=""friday"",TEXT(U2+" & x & ",""DDDD"")=""saturday""),0,1),"""")")
Range("C" & i) = Evaluate("=IF(M" & i & "=1,""08H00"","""")")
x = x + 1
Next


Range("A40") = Evaluate("=IF(V2>=29,29,"""")")
Range("A41") = Evaluate("=IF(V2>=30,30,"""")")
Range("A42") = Evaluate("=IF(V2>=31,31,"""")")
Range("E40") = Evaluate("=IF(AND(A40>=29,N40=0),""R.H"","""")")
Range("E41") = Evaluate("=IF(AND(A41>=30,N41=0),""R.H"","""")")
Range("E42") = Evaluate("=IF(AND(A42>=31,N42=0),""R.H"","""")")
End Sub

 

  • Like 1
محي الدين ابو البشر

محي الدين ابو البشر

قام بنشر
قام بنشر

ممكن تجرب 


    Range("U2") = Evaluate("=EOMONTH(TODAY(),-2)+1")
    Range("V2") = Evaluate("=DAY(DATE(YEAR($U$2),MONTH($U$2)+1,0))")
    Range("J5") = Evaluate("=UPPER(TEXT(U2,""[$-40c] mmmm yyyy""))")
    For i = 0 To 42 - 13
        Range("M" & 12 + i) = Evaluate("=IF(1<=$V$2,IF(OR(TEXT($U$2+" & i & ",""DDDD"")=""friday"",TEXT($U$2+" & i & ",""DDDD"")=""saturday""),0,1),"""")")
        Range("N" & 12 + i) = Evaluate("=IF(1<=$V$2,IF(OR(TEXT($U$2+" & i & ",""DDDD"")=""friday"",TEXT($U$2+" & i & ",""DDDD"")=""saturday""),0,1),"""")")
        Range("C" & 12 + i) = Evaluate("=IF(M" & 12 + i & "=1,""08H00"","""")")
    Next

    Range("E40") = Evaluate("=IF(AND(A40>=29,N40=0),""R.H"","""")")
    Range("E41") = Evaluate("=IF(AND(A41>=30,N41=0),""R.H"","""")")
    Range("E42") = Evaluate("=IF(AND(A42>=31,N42=0),""R.H"","""")")
    Range("A40") = Evaluate("=IF(V2>=29,29,"""")")
    Range("A41") = Evaluate("=IF(V2>=30,30,"""")")
    Range("A42") = Evaluate("=IF(V2>=31,31,"""")")


End Sub

 

محي الدين ابو البشر

محي الدين ابو البشر

قام بنشر
قام بنشر (معدل)

Sorry

  

Private Sub Workbook_Open()

    Range("U2") = Evaluate("=EOMONTH(TODAY(),-2)+1")
    Range("V2") = Evaluate("=DAY(DATE(YEAR($U$2),MONTH($U$2)+1,0))")
    Range("J5") = Evaluate("=UPPER(TEXT(U2,""[$-40c] mmmm yyyy""))")
    For i = 0 To 42 - 13
        Range("M" & 12 + i) = Evaluate("=IF(1<=$V$2,IF(OR(TEXT($U$2+" & i & ",""DDDD"")=""friday"",TEXT($U$2+" & i & ",""DDDD"")=""saturday""),0,1),"""")")
        Range("N" & 12 + i) = Evaluate("=IF(1<=$V$2,IF(OR(TEXT($U$2+" & i & ",""DDDD"")=""friday"",TEXT($U$2+" & i & ",""DDDD"")=""saturday""),0,1),"""")")
        Range("C" & 12 + i) = Evaluate("=IF(M" & 12 + i & "=1,""08H00"","""")")
        Range("F" & 12 + i) = Evaluate("=IF(M" & 12 + i & "=1,""16H30"","""")")
        If i <= 39 Then Range("E" & 12 + i) = Evaluate("=IF(N" & 12 + i & "=0,""R.H"","""")")
    Next
    Range("E40") = Evaluate("=IF(AND(A40>=29,N40=0),""R.H"","""")")
    Range("E41") = Evaluate("=IF(AND(A41>=30,N41=0),""R.H"","""")")
    Range("E42") = Evaluate("=IF(AND(A42>=31,N42=0),""R.H"","""")")
    Range("A40") = Evaluate("=IF(V2>=29,29,"""")")
    Range("A41") = Evaluate("=IF(V2>=30,30,"""")")
    Range("A42") = Evaluate("=IF(V2>=31,31,"""")")
End Sub

 

تم تعديل بواسطه محي الدين ابو البشر
لزهر مدلل

لزهر مدلل

قام بنشر
  • الكاتب
قام بنشر

السلام عليكم

معذرة مازال هناك مشكلتان الأولى مثلا في شهر فيفري فيه 28 يوم لكنه يقوم بتعبئة السطر 40 و 41 أي كأن الشهر 30 يوما و أيضا الشهور التي بها 31 يوما و يكون اليوم 31 ليس بيوم جمعة أو سبت لا يقوم بتعبئة السطر

تغيير الشهر يكون في هذا السطر عند 2- 

 Range("U2") = Evaluate("=EOMONTH(TODAY(),-2)+1")

أنظر الملف

 

Feuille de présence.xlsm

محي الدين ابو البشر

محي الدين ابو البشر

قام بنشر
قام بنشر

عسى تكون المطلوب 

Private Sub Workbook_Open()
    Range("C12:N42").ClearContents
    Range("U2") = Evaluate("=EOMONTH(TODAY(),-2)+1")
    Range("V2") = Evaluate("=DAY(DATE(YEAR($U$2),MONTH($U$2)+1,0))")
    Range("J5") = Evaluate("=UPPER(TEXT(U2,""[$-40c] mmmm yyyy""))")
    For i = 0 To Range("V2").Value - 1
        Range("M" & 12 + i) = Evaluate("=IF(1<=$V$2,IF(OR(TEXT($U$2+" & i & ",""DDDD"")=""friday"",TEXT($U$2+" & i & ",""DDDD"")=""saturday""),0,1),"""")")
        Range("N" & 12 + i) = Evaluate("=IF(1<=$V$2,IF(OR(TEXT($U$2+" & i & ",""DDDD"")=""friday"",TEXT($U$2+" & i & ",""DDDD"")=""saturday""),0,1),"""")")
        Range("C" & 12 + i) = Evaluate("=IF(M" & 12 + i & "=1,""08H00"","""")")
        Range("F" & 12 + i) = Evaluate("=IF(M" & 12 + i & "=1,""16H30"","""")")
        Range("E" & 12 + i) = Evaluate("=IF(N" & 12 + i & "=0,""R.H"","""")")
    Next
    Range("E40") = Evaluate("=IF(AND(A40>=29,N40=0),""R.H"","""")")
    Range("E41") = Evaluate("=IF(AND(A41>=30,N41=0),""R.H"","""")")
    Range("E42") = Evaluate("=IF(AND(A42>=31,N42=0),""R.H"","""")")
    Range("A40") = Evaluate("=IF(V2>=29,29,"""")")
    Range("A41") = Evaluate("=IF(V2>=30,30,"""")")
    Range("A42") = Evaluate("=IF(V2>=31,31,"""")")
End Sub

 

Join the conversation

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

زائر
اضف رد علي هذا الموضوع....

×   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.

×
 مشاركة
الذهاب الي قائمه المواضيع

  • تصفح هذا الموضوع مؤخراً   0 اعضاء متواجدين الان

    • لايوجد اعضاء مسجلون يتصفحون هذه الصفحه
×
×
  • اضف...

Important Information

  I accept