محمد هشام.

وعليكم السلام ورحمة الله تعالى وبركاته حل اخر بإستخدام الأكواد Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim lastRow As Long, i As Long, n As Long, tmp As Variant, a As Object If Intersect(Target, Me.Columns("A:B")) Is Nothing Then Exit Sub Application.EnableEvents = False Application.ScreenUpdating = False On Error Resume Next lastRow = Me.Columns("A:B").Find(What:="*", _ SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row On Error GoTo 0 If lastRow < 2 Then GoTo CleanUp Me.Range("A2:A" & lastRow).ClearContents tmp = Me.Range("B2:B" & lastRow).Value: Set a = CreateObject("Scripting.Dictionary") For i = 1 To UBound(tmp) If Len(Trim(tmp(i, 1))) > 0 And Not a.exists(tmp(i, 1)) Then n = n + 1: a(tmp(i, 1)) = n: Me.Cells(i + 1, "A").Value = n End If Next i CleanUp: Application.ScreenUpdating = True Application.EnableEvents = True End Sub ترقيم بتجاوز المكرر.xlsb

وعليكم السلام ورحمة الله تعالى وبركاته جرب هدا Option Explicit Sub FindStagnantItems() Const stagnantPeriod As Integer = 90 Dim WS As Worksheet, dest As Worksheet, ShArr As Variant, Ky As Object Dim lastRow As Long, i As Long, d As Object, cate As Variant, Irow As Long Dim item As String, Store As String, Movement As Date, C As Variant ShArr = Array("مخزن الرئيسي", "فرع 1") Set d = CreateObject("Scripting.Dictionary"): Set Ky = CreateObject("Scripting.Dictionary") For Each C In ShArr On Error Resume Next Set WS = ThisWorkbook.Sheets(C) On Error GoTo 0 If WS Is Nothing Then MsgBox "خطأ في الوصول إلى الورقة: " & C, vbCritical: Exit Sub Application.ScreenUpdating = False lastRow = WS.Cells(WS.Rows.Count, 1).End(xlUp).Row For i = 2 To lastRow item = WS.Cells(i, 1).Value: Store = WS.Cells(i, 4).Value: Movement = WS.Cells(i, 3).Value If item <> "" And Store <> "" Then If IsDate(Movement) Then If DateDiff("d", Movement, Date) > stagnantPeriod Then If Not d.Exists(Store) Then d.Add Store, New Collection If Not n(d(Store), item) Then d(Store).Add item Ky(Store) = Ky(Store) + 1 End If End If End If Next i Next C On Error Resume Next: Set dest = Worksheets("أصناف راكدة"): On Error GoTo 0 If dest Is Nothing Then Set dest = Worksheets.Add: dest.Name = "أصناف راكدة" Else dest.Cells.ClearContents End If dest.[A1].Resize(1, 2) = Array("التصنيف", "عدد الأصناف الراكدة") Irow = 2 For Each cate In Ky.keys dest.Cells(Irow, 1).Value = cate dest.Cells(Irow, 2).Value = Ky(cate) Irow = Irow + 1 Next cate Application.ScreenUpdating = True MsgBox "تم إنشاء تقرير الأصناف الراكدة بنجاح", vbInformation End Sub Function n(col As Collection, val As String) As Boolean On Error Resume Next n = Not IsError(col(val)) End Function الاصناف الراكدة لكل مخزن.xlsm

يسعدنا أخي @محمد نوح أننا إستطعنا مساعدتك

أظن أنه يمكننا إضافة شرط التحقق من كلمة المرور عند محاولة غلق الحسابات في الكود بحيث لا يمكن لأي شخص تنفيذه إلا إذا كان يعرف كلمة المرور الصحيحة هذا يضيف طبقة أمان إضافية للحماية ويضمن أن الشخص الذي يقوم بالعملية هو الشخص المخول فقط جرب هدا التعديل Option Explicit Private Const Clé As String = "1234" Public Property Get WS() As Worksheet Set WS = Sheets("Sheet1") End Property Sub ProtectSheet(xligne As Long) With WS .Unprotect Password:=Clé: .Cells.Locked = False .Range("A2:M" & xligne).FormulaHidden = True .Range("A2:M" & xligne).Locked = True: .Protect Password:=Clé End With End Sub Sub WSUnprotect() With WS .Unprotect Password:=Clé .Cells.Locked = False .Cells.FormulaHidden = False End With End Sub Sub Data_Protection() Dim xligne As Long If InputBox("أدخل كلمة المرور للمتابعة") <> Clé Then MsgBox "كلمة المرور غير صحيحة تم إلغاء العملية", vbCritical Exit Sub End If xligne = Application.InputBox("أدخل رقم الصف الأخير لقفل الخلايا", Type:=1) If xligne < 1 Or xligne > WS.Rows.Count Then MsgBox "خطأ في الإدخال يرجى إدخال رقم صف صحيح", vbExclamation Exit Sub End If SetApp False ProtectSheet xligne SetApp True MsgBox "تم قفل الحسابات بنجاح لغاية الصف: " & xligne, vbInformation End Sub Sub Data_UnProtection() Dim PassProtect As String PassProtect = InputBox("أدخل كلمة المرور لفك الحماية") If PassProtect = Clé Then SetApp False: WSUnprotect: SetApp True MsgBox "تم فتح جميع الحسابات بنجاح", vbInformation ElseIf PassProtect <> "" Then MsgBox "كلمة المرور غير صحيحة", vbCritical End If End Sub Private Sub SetApp(ByVal enable As Boolean) On Error GoTo xError Application.ScreenUpdating = enable Application.EnableEvents = enable Application.Calculation = IIf(enable, xlCalculationAutomatic, xlCalculationManual) Exit Sub xError: End Sub غلق المدى المحدد .xlsb

وعليكم السلام ورحمة الله تعالى وبركاته جرب هدا في Module ضع الكود التالي Sub ColoriageDoublons() Dim WSarr As Variant, couleurs As Long, d As Object, _ s As Variant, OnRng As Range, lastRow As Long, a, i As Long WSarr = Array(1, 2, 3): couleurs = RGB(0, 204, 255) Set d = CreateObject("Scripting.Dictionary") For Each s In WSarr With Sheets(s) lastRow = .Cells.Find(What:="*", LookIn:=xlValues, _ SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row a = .Range("C4:C" & lastRow).Value For i = 1 To UBound(a, 1) If a(i, 1) <> "" Then d(a(i, 1)) = d(a(i, 1)) + 1 Next i End With Next s For Each s In WSarr With Sheets(s) lastRow = .Cells.Find(What:="*", LookIn:=xlValues, _ SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row Set OnRng = .Range("C4:C" & lastRow) a = OnRng.Value For i = 1 To UBound(a, 1) OnRng.Cells(i).Interior.Color = IIf(a(i, 1) <> "" And d(a(i, 1)) > 1, couleurs, xlNone) Next i End With Next s End Sub وفي حدث ThisWorkbook Option Explicit Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim WSarr As Variant WSarr = Array("1", "2", "3") If Not Intersect(Target, Sh.Columns("C")) Is Nothing And Target.Row >= 4 Then Application.ScreenUpdating = False If Not IsError(Application.Match(Sh.Name, WSarr, 0)) Then Call ColoriageDoublons End If Application.ScreenUpdating = True End If End Sub تلوين الخلايا v2 المكررة.xlsm

وعليكم السلام ورحمة الله تعالى وبركاته Sub SaveAsPDF() Const Max As Long = 1000 Dim WS As Worksheet, Irow As Long, OnRng As Range Dim xPath As String, Dossier As String, Fichier As String Set WS = Sheets("Sheet1") Irow = WS.Cells(WS.Rows.Count, "A").End(xlUp).Row If Irow > Max Then Irow = Max: Set OnRng = WS.Range("A2:Z" & Irow) If Application.WorksheetFunction.CountA(OnRng) = 0 Then Exit Sub WS.ResetAllPageBreaks With WS.PageSetup .PrintArea = OnRng.Address: .Orientation = xlPortrait: .PaperSize = xlPaperA4 .Zoom = False: .FitToPagesWide = 1: .FitToPagesTall = False End With Dossier = ThisWorkbook.Path & "\ملفات PDF" If Dir(Dossier, vbDirectory) = "" Then MkDir Dossier Fichier = Replace(WS.Range("AA1").Value, "/", "_") xPath = Dossier & "\" & Fichier & " " & Format(Now, "yyyy-mm-dd hh.mm") & ".pdf" WS.ExportAsFixedFormat Type:=xlTypePDF, FileName:=xPath, _ Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False WS.PageSetup.PrintArea = "" MsgBox "تم حفظ الملف بنجاح ", vbInformation End Sub Test-PDF.xlsb

وعليكم السلام ورحمة الله تعالى وبركاته جرب هدا Book1.xlsx

وعليكم السلام ورحمة الله تعالى وبركاته جرب هدا حفظ الملفات في تفس مسار الملف Option Explicit Sub SAVE_PDF() Dim ScWS As Variant, Path As String, i As Integer ScWS = Array("Sheet1", "Sheet2", "Sheet3") Path = ThisWorkbook.Path & "\" If Path = "\" Then Exit Sub For i = LBound(ScWS) To UBound(ScWS) If Not ShExists(ScWS(i)) Then MsgBox "الورقة " & ScWS(i) & " غير موجودة": Exit Sub Application.ScreenUpdating = False On Error Resume Next Sheets(ScWS(i)).ExportAsFixedFormat Type:=xlTypePDF, FileName:=Path & ScWS(i) & ".pdf", _ Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False On Error GoTo 0 Next i Application.ScreenUpdating = True MsgBox "تم حفظ الملفات بنجاح" End Sub لإنشاء مجلد وحفظ الملفات بداخله Sub SAVE_PDF_Folder() Dim ScWS As Variant, Path As String, Dossier As String, i As Integer ScWS = Array("Sheet1", "Sheet2", "Sheet3") Path = ThisWorkbook.Path & "\" Dossier = "ملفات PDF" If Path = "\" Then Exit Sub If Dir(Path & Dossier, vbDirectory) = "" Then MkDir Path & Dossier Path = Path & Dossier & "\" For i = LBound(ScWS) To UBound(ScWS) If Not ShExists(ScWS(i)) Then MsgBox "الورقة " & ScWS(i) & " غير موجودة": Exit Sub Application.ScreenUpdating = False On Error Resume Next Sheets(ScWS(i)).ExportAsFixedFormat Type:=xlTypePDF, FileName:=Path & ScWS(i) & ".pdf", _ Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False On Error GoTo 0 Next i Application.ScreenUpdating = True MsgBox "تم حفظ الملفات بنجاح" End Sub Function ShExists(ByVal SheetName As String) As Boolean On Error Resume Next: ShExists = Not Sheets(SheetName) Is Nothing: On Error GoTo 0 End Function حفظ الملفات مستقلة بصيغة PDF.xlsb

وعليكم السلام ورحمة الله تعالى وبركاته جرب هدا Option Explicit Sub Transfer() Dim srcRange As Range, Lr As Long, destCols As Variant Dim WS As Worksheet, dest As Worksheet, i As Integer Dim a(1 To 1, 1 To 7) As Variant Set WS = ActiveSheet Set dest = Sheets("كشف الحساب") a(1, 1) = WS.[B6].Value: a(1, 2) = WS.[C6].Value: a(1, 3) = WS.[D6].Value a(1, 4) = WS.[E6].Value: a(1, 5) = WS.[G6].Value: a(1, 6) = WS.[H6].Value: a(1, 7) = WS.[I6].Value destCols = Array("C", "D", "E", "F", "H", "I", "J") Lr = dest.Cells(dest.Rows.Count, "D").End(xlUp).Row + 1 For i = 0 To 6 dest.Cells(Lr, destCols(i)).Value = a(1, i + 1) Next i End Sub """""""""""""""""""""""""""""""""""""""""""""""""""""""""" Sub testCopy() Dim i As Integer, ScrWS As Worksheet, btn As Object Dim Sh As Worksheet: Set Sh = Sheets("البون") Application.ScreenUpdating = False Application.DisplayAlerts = False Application.CutCopyMode = False For i = 1 To 15 On Error Resume Next Set ScrWS = ThisWorkbook.Sheets(Sh.Name & i) If Not ScrWS Is Nothing Then ScrWS.Delete Next i For i = 1 To 15 Sh.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count) Set ScrWS = ActiveSheet ScrWS.Name = Sh.Name & i ScrWS.DisplayRightToLeft = True For Each btn In ScrWS.Buttons: btn.Delete: Next btn On Error GoTo 0 Set btn = ScrWS.Buttons.Add(400, 20, 60, 30): btn.OnAction = "Transfer": btn.Caption = "ترحيل" Next i Sh.Activate Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub الايرادات والمصروفات.xlsm

جرب هدا Private Sub Worksheet_Change(ByVal Target As Range) Dim Lr As Long Dim WS As Worksheet: Set WS = Sheets("فاتورة مبيعات") Lr = WS.Cells(WS.Rows.Count, "F").End(xlUp).Row Application.EnableEvents = False For Each tmp In Target If Not Intersect(tmp, WS.Columns("F")) Is Nothing Or Not Intersect(tmp, WS.Columns("E")) Is Nothing Then If tmp.Row <= Lr Then WS.Cells(tmp.Row, "G").Formula = "=IF(AND(F" & tmp.Row & "<>"""", E" & _ tmp.Row & "<>""""), F" & tmp.Row & "*E" & tmp.Row & ", """")" End If End If Next tmp Application.EnableEvents = True Exit Sub Application.EnableEvents = True End Sub او Private Sub Worksheet_Change(ByVal Target As Range) Dim ColArr As Long, a As Variant, i As Long Dim WS As Worksheet: Set WS = Me On Error GoTo SubApp Application.EnableEvents = False Application.Calculation = xlCalculationManual ColArr = WS.Columns("E:G").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row If Not Intersect(Target, WS.Range("E5:F" & ColArr)) Is Nothing Then a = WS.Range("E5:G" & ColArr).Value With WS For i = 1 To ColArr - 4 If IsNumeric(a(i, 1)) And IsNumeric(a(i, 2)) Then If Len(a(i, 1)) > 0 And Len(a(i, 2)) > 0 Then a(i, 3) = a(i, 1) * a(i, 2) Else a(i, 3) = "" End If Else a(i, 3) = "" End If Next i .Range("E5:G" & ColArr).Value = a End With End If SubApp: Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic End Sub طط.rar النتيجة قيم طط.rar

أخي @Hussein888 في Excel 365 يوجد خاصية تلقائية تعرف بـ AutoComplete التي تجعل القوائم المنسدلة تتفاعل بشكل ديناميكي مع الحروف التي تكتبها في الخلية حيث يتم تحديث القائمة لتظهر القيم التي تطابق ما كتبته لكن في Excel 2016 لا توجد هذه الخاصية بشكل افتراضي في القوائم المنسدلة المعتمدة على Data Validation ولكن هناك حل بديل باستخدام VBA كما في المثال التالي بما أنك لم تقم بإرفاق ملفك لتحديد النطاقات المطلوبة إليك الكود يمكنك تعديله بما يناسبك Option Compare Text Dim a() Private Sub Worksheet_SelectionChange(ByVal Target As Range) ' تحديد نطاق القوائم المنسدلة If Not Intersect([A2:A10], Target) Is Nothing And Target.Count = 1 Then ' (الأسماء) تحديد نطاق البيانات Set Rng = Range("C2:C" & Cells(Rows.Count, "C").End(xlUp).Row) Set tbl = CreateObject("Scripting.Dictionary") tbl.CompareMode = vbTextCompare For Each c In Rng If c.Value <> "" Then tbl(c.Value) = "" Next c a = tbl.Keys 'ترتيب ابجدي tri a, 1, UBound(a) With Me.ComboBox1 .List = a: .Top = Target.Top: .Left = Target.Left: .Width = Target.Width .Height = Target.Height + 3: .Visible = True: .Activate End With Else Me.ComboBox1.Visible = False End If End Sub Private Sub ComboBox1_Change() If Me.ComboBox1.Text <> "" Then Set tbl = CreateObject("Scripting.Dictionary") tmp = "*" & UCase(Me.ComboBox1.Text) & "*" ' البحث عن النص في أي مكان For Each c In a If UCase(c) Like tmp Then tbl(c) = "" Next c Me.ComboBox1.List = tbl.Keys Me.ComboBox1.DropDown End If ActiveCell.Value = Me.ComboBox1.Text End Sub Private Sub ComboBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean) Me.ComboBox1.List = a Me.ComboBox1.Activate Me.ComboBox1.DropDown End Sub Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) If KeyCode = 13 Then ActiveCell.Offset(1).Select End Sub Sub tri(a, gauc, droi) ref = a((gauc + droi) \ 2) g = gauc: d = droi Do Do While a(g) < ref: g = g + 1: Loop Do While ref < a(d): d = d - 1: Loop If g <= d Then temp = a(g): a(g) = a(d): a(d) = temp g = g + 1: d = d - 1 End If Loop While g <= d If g < droi Then Call tri(a, g, droi) If gauc < d Then Call tri(a, gauc, d) End Sub Test-Data Validation.xlsb

المفروض ان تضع عينة للنتائج المتوقعة على ورقة العميل للتأكد من الخلايا المرحلة والأعمدة المرحل اليها حاول تعديل الكود التالي بما يناسبك Sub test() Dim srcWS As Worksheet, dest As Worksheet Dim f As String, Lr As Long Dim a(1 To 1, 1 To 3) As Variant Set srcWS = Sheets("تسجيل") f = srcWS.Range("C8").Value On Error Resume Next Set dest = ThisWorkbook.Sheets(f) On Error GoTo 0 If dest Is Nothing Then: MsgBox "ورقة العميل '" & f & "' غير موجودة", vbExclamation: Exit Sub If srcWS.Range("C7").Value = "اجل" Then a(1, 1) = Format(Date, "dd/mm/yyyy") ' التاريخ a(1, 2) = srcWS.[C4].Value & " " & srcWS.[C5].Value 'الوصف مع الكود a(1, 3) = srcWS.[C6].Value 'سعر البيع Lr = dest.Cells(dest.Rows.Count, "B").End(xlUp).Row + 1 dest.Range(dest.Cells(Lr, "B"), dest.Cells(Lr, "D")).Value = a MsgBox "تم الترحيل بنجاح إلى ورقة العميل " & f, vbInformation End If End Sub

وعليكم السلام ورحمة الله تعالى وبركاته لم توضح أخي @علي بطيخ سالم هل الزيادة ستنفد على العمودين معا او الى فقط اليك محاولة مني بالاكواد لتثبيث الزيادة في عمود ( من) بساعة دائما وزيادة مدة الدقائق المدخلة في عمود (الى) Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim crWS As Worksheet: Set crWS = Me Dim tmp As Date, n As Double, lastRow As Long, i As Long If Not Intersect(Target, crWS.Range("A2,B2")) Is Nothing Then If crWS.Range("A2").Value = "" Or Application.WorksheetFunction.IsText(crWS.Range("A2").Value) Then _ MsgBox "يرجى إدخال توقيت البداية", vbExclamation: Exit Sub If Not IsNumeric(crWS.Range("B2").Value) Or _ crWS.Range("B2").Value <= 0 Then: MsgBox "يرجى إدخال مدة الزيادة بالدقائق", vbExclamation: Exit Sub tmp = crWS.Range("A2").Value n = crWS.Range("B2").Value / 1440 lastRow = crWS.Cells(crWS.Rows.Count, "E").End(xlUp).Row If lastRow < 2 Then Exit Sub Application.ScreenUpdating = False crWS.Range("C2:D" & crWS.Cells(crWS.Rows.Count, "C").End(xlUp).Row).ClearContents For i = 2 To lastRow If crWS.Cells(i, "E").Value <> "" Then crWS.Cells(i, "C").Value = Format(tmp, "hh:mm") crWS.Cells(i, "D").Value = Format(tmp + n, "hh:mm") tmp = tmp + TimeSerial(1, 0, 0) ' لتنفيد الزيادة بالدقائق على العمودين من و الى ' tmp = tmp + n End If Next i Application.ScreenUpdating = True End If End Sub في حالة الرغبة بتنفيد الزيادة على العمودين tmp = tmp + n توقيت البداية.xlsb

ادن جرب الملف المرفق في المشاركة السابقة بعد التعديل '========= استبدل هدا '============== b = Left(j, Len(j) - Len(CStr(Val(j)))) Cnt = Val(Right(j, Len(j) - Len(b))) newCode = b & Cnt + 1 '====== بالكود التالي '========== Dim tmp As String, textPart As String For i = Len(j) To 1 Step -1 If IsNumeric(Mid(j, i, 1)) Then tmp = Mid(j, i, 1) & tmp Else textPart = Left(j, i) Exit For End If Next i

Sub TransferData2() Dim i As Long, Cnt As Long Dim ws As Worksheet, f As Worksheet, sWS As Worksheet Dim Sh As String, arr As Variant Dim tbl As ListObject, a As Range, lige As Range Dim j As String, newCode As String, b As String Set ws = ThisWorkbook.Sheets("تسجيل") Sh = ws.[G3].Value arr = Array(ws.[G4], ws.[G5], ws.[G6], ws.[G7]) For i = 0 To 3 If arr(i) = "" Then MsgBox "يرجى إدخال: " & arr(i).Offset(0, -1), vbExclamation, "إنتباه" ws.Activate: arr(i).Select Exit Sub End If Next On Error Resume Next Set f = ThisWorkbook.Sheets(Sh) On Error GoTo 0 If f Is Nothing Then MsgBox "قائمة المخزون " & Sh & " غير موجودة", vbExclamation Exit Sub End If If MsgBox("هل ترغب في ترحيل بيانات التسجيل؟", vbYesNo + vbQuestion, "تأكيد الترحيل") = vbNo Then Exit Sub Set tbl = f.ListObjects(1) On Error Resume Next Set lige = tbl.ListColumns(2).DataBodyRange.SpecialCells(xlCellTypeConstants).Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious) On Error GoTo 0 ' الكود الجديد If Not lige Is Nothing Then j = lige.Value '========= استبدل هدا '============== ' b = Left(j, Len(j) - Len(CStr(Val(j)))) ' Cnt = Val(Right(j, Len(j) - Len(b))) ' newCode = b & Cnt + 1 '====== بالكود التالي '========== Dim tmp As String, textPart As String For i = Len(j) To 1 Step -1 If IsNumeric(Mid(j, i, 1)) Then tmp = Mid(j, i, 1) & tmp Else textPart = Left(j, i) Exit For End If Next i If tmp <> "" Then Cnt = CLng(tmp) Else Cnt = 0 End If newCode = textPart & (Cnt + 1) Else newCode = ws.[G4].Value End If If Not lige Is Nothing Then Set a = lige.Offset(1, 0) Else Set a = tbl.ListColumns(2).DataBodyRange.Cells(1, 1) If a.Value <> "" Then Set a = tbl.ListColumns(2).DataBodyRange.Cells(tbl.ListRows.Count + 1, 1) End If End If a.Value = newCode ' الكود a.Offset(0, 5).Value = 1 ' الكمية a.Offset(0, 2).Value = arr(1) ' الاسم a.Offset(0, 3).Value = arr(2) ' الوصف a.Offset(0, 7).Value = arr(3) ' الملاحظات a.Offset(0, 9).Value = Format(Date, "dd/mmmm") ' التاريخ Set sWS = Sheets("المشتريات") Set tbl = sWS.ListObjects(1) On Error Resume Next Set lige = tbl.ListColumns(3).Range.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious) On Error GoTo 0 If Not lige Is Nothing Then Set a = lige.Offset(1, 0) Else Set a = tbl.ListColumns(3).DataBodyRange.Cells(1, 1) If a.Value <> "" Then Set a = tbl.ListColumns(3).DataBodyRange.Cells(tbl.ListRows.Count + 1, 1) End If End If a.Cells(1, 1).Offset(0, -1).Value = Format(Date, "dd/mmmm") ' التاريخ a.Value = newCode ' الكود a.Offset(0, 5).Value = 1 ' الكمية a.Offset(0, 2).Value = arr(1) ' الاسم a.Offset(0, 3).Value = arr(2) ' الوصف a.Offset(0, 7).Value = arr(3) ' الملاحظات a.Offset(0, 9).Value = Format(Date, "dd/mmmm") ' التاريخ End Sub مبيعات ومشتريات V3.xlsb

وعليكم السلام ورحمة الله تعالى وبركاته جرب هدا Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim n As Range, f As String, count As Integer, i As Integer If Not Intersect(Target, Me.Range("A1:B2")) Is Nothing Then Dim WS As Worksheet: Set WS = Sheets("data") Dim xRow As Range: Set xRow = WS.Range("A1:J1") Dim tmp As Integer: tmp = xRow.Column xRow.ClearContents For Each n In Me.Range("A1:A2") If n.Value <> "" Then f = n.Value count = n.Offset(0, 1).Value For i = 1 To count If tmp > xRow.Columns.count + xRow.Column - 1 Then Exit Sub WS.Cells(xRow.Row, tmp).Value = f tmp = tmp + 1 Next i End If Next n End If End Sub test2.xlsb

وعليكم السلام ورحمة الله تعالى وبركاته Sub MergeTotal() Dim WS As Worksheet, crWS As Worksheet, LastRow As Long, Irow As Long On Error Resume Next Set crWS = Sheets("total") On Error GoTo 0 If crWS Is Nothing Then MsgBox " غير موجودة total ورقة ", vbInformation Exit Sub Else Application.ScreenUpdating = False crWS.Range("A2:O" & crWS.Rows.Count).Clear End If Irow = 2 For Each WS In ThisWorkbook.Sheets If WS.Name <> crWS.Name Then LastRow = WS.Cells(WS.Rows.Count, 1).End(xlUp).Row If LastRow >= 2 Then WS.Range("A2:O" & LastRow).Copy crWS.Cells(Irow, 1).PasteSpecial Paste:=xlPasteAllUsingSourceTheme Irow = crWS.Cells(crWS.Rows.Count, 1).End(xlUp).Row + 1 End If End If Next WS Application.CutCopyMode = False Application.ScreenUpdating = True End Sub or Sub MergeTotal() Dim WS As Worksheet, Src As Worksheet Dim OnRng As Variant, rng As Range, r As Range Dim lastRow As Long, tmp As Long, col As Integer Set WS = Sheets("total") Application.ScreenUpdating = False lastRow = WS.Cells(WS.Rows.Count, "A").End(xlUp).Row If lastRow > 1 Then: WS.Rows("2:" & lastRow).Clear tmp = WS.Cells(WS.Rows.Count, "A").End(xlUp).Row + 1 For Each Src In ThisWorkbook.Sheets If Src.Name <> WS.Name Then OnRng = Src.Range("A2:O" & Src.Cells(Src.Rows.Count, "A").End(xlUp).Row).Value WS.Cells(tmp, 1).Resize(UBound(OnRng, 1), UBound(OnRng, 2)).Value = OnRng For lastRow = 1 To Src.Cells(Src.Rows.Count, "A").End(xlUp).Row WS.Rows(tmp + lastRow - 1).RowHeight = 18.5 Next lastRow tmp = WS.Cells(WS.Rows.Count, "A").End(xlUp).Row + 1 End If Next Src With WS.Range("A1:O" & WS.Cells(WS.Rows.Count, "A").End(xlUp).Row) .Borders.LineStyle = xlContinuous: .HorizontalAlignment = xlCenter: .VerticalAlignment = xlCenter End With Application.ScreenUpdating = True End Sub الرواتب.xlsb

وعليكم السلام ورحمة الله تعالى وبركاته بعد إذن الأستاد @عبدالله بشير عبدالله بما ان الكود الخاص به يعتمد على التنسيق إليك حل آخر باظهار رسالة تنبيه عند تجاوز الحد الاقصى للتكرارات بشرط التاريخ في عمود b [نفس الشهر ] واسم المهنة عمود d والحالة في عمود F طريقة الإدخال الكود_ المهنة _الحالة Private Sub Worksheet_Change(ByVal Target As Range) Dim lastRow As Long, Max As Integer, kay As Variant, xdate As Variant Max = 5 Application.EnableEvents = False lastRow = Cells(Rows.Count, 4).End(xlUp).Row If Not Intersect(Target, Me.Columns("B")) Is Nothing Then For Each cell In Target If cell.Value <> "" And IsEmpty(cell.Offset(0, 1).Value) Then cell.Offset(0, 1).Value = Date Else cell.Offset(0, 1).Value = "" End If Next cell End If If Not Intersect(Target, Me.Range("C5:F" & lastRow)) Is Nothing Then For Each cell In Target If cell.Column = 6 And cell.Value = "إجازة" Then kay = cell.Offset(0, -2).Value xdate = cell.Offset(0, -3).Value If IsEmpty(kay) Or IsEmpty(xdate) Then MsgBox "يجب إدخال كود الموظف", vbExclamation, "إنتبـــــاه" cell.ClearContents GoTo SupAPP End If If WorksheetFunction.CountIfs(Range("D5:D" & _ lastRow), kay, Range("F5:F" & lastRow), "إجازة", Range("C5:C" & lastRow), xdate) > 1 Then cell.ClearContents MsgBox " تم الوصول للحد الأقصى للإجازات هدا الشهر لسائقين :" & _ " " & kay, vbExclamation, "إنتبـــــاه" GoTo SupAPP End If If WorksheetFunction.CountIfs(Range("D5:D" & _ lastRow), kay, Range("F5:F" & lastRow), "إجازة", Range("C5:C" & lastRow), ">=" & WorksheetFunction.EoMonth(xdate, -1) + 1, _ Range("C5:C" & lastRow), "<=" & WorksheetFunction.EoMonth(xdate, 0)) > Max Then cell.ClearContents MsgBox "وصلت للحد الأقصى لهذا الشهر في إجازات السائق: " & kay, vbExclamation, "إنتبـــــاه" End If End If SupAPP: Next cell End If Application.EnableEvents = True End Sub اجاز V1.xlsb

وعليكم السلام ورحمة الله تعالى وبركاته بعد إدن الأستاد @عبدالله بشير عبدالله إليك حلول أخرى =IF(A1>3000, A1*IF(B1<=500, 1.5, 2.5), IF(B1<500, 6000, "")) =IFERROR(IFS( AND(A1>3000, B1<=500), A1*1.5, AND(A1>3000, B1>500), A1*2.5, AND(A1<=3000, B1<500), 6000, TRUE, "" ), "") او Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("A:B")) Is Nothing Then Dim data As Variant, tmp() As Variant, lastRow As Long, i As Long Dim a As Double: a = 3000: b = 500: c = 6000 lastRow = Me.Cells(Me.Rows.Count, "A").End(xlUp).Row data = Me.Range("A1:B" & lastRow).Value ReDim tmp(1 To UBound(data, 1), 1 To 1) For i = 1 To UBound(data, 1) If IsEmpty(data(i, 1)) Or IsEmpty(data(i, 2)) Then tmp(i, 1) = "" ElseIf IsNumeric(data(i, 1)) And IsNumeric(data(i, 2)) Then If data(i, 1) > a Then tmp(i, 1) = IIf(data(i, 2) <= b, data(i, 1) * 1.5, data(i, 1) * 2.5) ElseIf data(i, 2) < b Then tmp(i, 1) = c Else tmp(i, 1) = "" End If Else tmp(i, 1) = "" End If Next i Me.Range("C1:C" & lastRow).Value = tmp End If End Sub test2025.xlsb

وعليكم السلام ورحمة الله تعالى وبركاته ادا كان هدا ما تقصده جرب هدا =IFERROR(TEXT(DATE(2000+LEFT(B2,2),MID(B2,3,2),RIGHT(B2,2)),"DD/MM/YYYY"),"") او Option Explicit Sub ConvertDate() Dim lr As Long, r As Long, xDate As String, n As String Dim scWS As Worksheet: Set scWS = Sheets("Sheet1") lr = scWS.Cells(scWS.Rows.Count, "B").End(xlUp).Row For r = 2 To lr xDate = scWS.Cells(r, "B").Value If xDate <> "" Then n = Format(DateSerial(2000 + Left(xDate, 2), _ Mid(xDate, 3, 2), _ Right(xDate, 2)), "dd/mm/yyyy") scWS.Cells(r, "D").Value = n End If Next r End Sub New Microsoft Excel Worksheet.xlsx

نعم اخي @hanykassem نظرا للمثال المرفق هناك بعض الإحتمالات الواردة في حالة كان هناك تكرار لنفس القيم كما هو موضح في الصورة أدناه Private Sub Worksheet_Change(ByVal Target As Range) Dim WS As Worksheet: Set WS = Sheets("Sheet1") Dim i As Long, ling As Long, lastRow As Long, tmp As String, kayB As String, kayC As String, _ j As Variant, a As Object, r As Object Set a = CreateObject("Scripting.Dictionary"): Set r = CreateObject("Scripting.Dictionary") If Not Intersect(Target, WS.Range("A4:C" & WS.Rows.Count)) Is Nothing Then Application.ScreenUpdating = False With WS .Range("I3:K" & .Rows.Count).ClearContents lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row ling = 3 For i = 4 To lastRow tmp = .Cells(i, 1).value kayB = .Cells(i, 2).value kayC = .Cells(i, 3).value If tmp <> "" Then If kayB <> "" Then a(tmp) = IIf(a.Exists(tmp), a(tmp) & " , " & kayB, kayB) If kayC <> "" Then r(tmp) = IIf(r.Exists(tmp), r(tmp) & " , " & kayC, kayC) End If Next i For Each j In a.Keys .Cells(ling, 9).value = j .Cells(ling, 10).value = a(j) .Cells(ling, 11).value = r(j) ling = ling + 1 Next j .Columns("j:K").AutoFit End With Application.ScreenUpdating = True End If End Sub لحدف التكرارات قم بتعديل الصف التالي If tmp <> "" Then If kayB <> "" Then a(tmp) = IIf(a.Exists(tmp), a(tmp) & " , " & kayB, kayB) If kayC <> "" Then r(tmp) = IIf(r.Exists(tmp), r(tmp) & " , " & kayC, kayC) End If إلى If tmp <> "" Then If kayB <> "" Then If Not a.Exists(tmp) Then a.Add tmp, _ kayB Else If InStr(1, a(tmp), kayB) = 0 Then a(tmp) = a(tmp) & " , " & kayB If kayC <> "" Then If Not r.Exists(tmp) Then r.Add tmp, _ kayC Else If InStr(1, r(tmp), kayC) = 0 Then r(tmp) = r(tmp) & " , " & kayC End If TEST CODE 2.xlsb

يمكنك تعديل كود عرض الأعمدة بترتيب العناصر على الشكل التالي Private Sub ContrArr(tmp As Long) Dim controls As Variant, columns As Variant, i As Integer controls = Array("TextBox7", "ComboBox1", "ComboBox2", "ComboBox3", "ComboBox4", _ "TextBox3", "TextBox4", "TextBox5", "TextBox6", "ComboBox5") columns = Array(2, 4, 5, 6, 7, 8, 9, 10, 11, 12) If Me.TextBox8.Text = "" Then ClearControls Else Me.TextBox8.Tag = tmp For i = LBound(controls) To UBound(controls) Me.controls(controls(i)).Text = WS.Cells(tmp, columns(i)).Value Next i tblUpdate tmp End If End Sub البحث والتنقل.rar

وعليكم السلام ورحمة الله تعالى وبركاته ربما هدا ما تقصده ادا كنت قد فهمت طلبك بشكل صحيح يمكنك حدف جميع الأكواد السابقة فهدا سيوفي بالغرض بعد إظافة عنصر Label جديد بإسم Label15 لإظهار عدد السجلات كما في الصورة المرفقة Public WS As Worksheet Private Sub UserForm_Initialize() Set WS = Sheets("تسجيل البيانات") End Sub Private Sub Navigation(r As Integer) Dim NInvoice As String, tmp As Long, Col As Long NInvoice = Trim(TextBox8.Text) If NInvoice = "" Then MsgBox "يرجى إدخال رقم الفاتورة", vbExclamation Exit Sub End If tmp = TextBox8.Tag Col = FndRow(NInvoice, tmp, r) If Col = 0 Then MsgBox TextBox8.Value & " : " & "لا يوجد سجلات " & IIf(r = 1, "لاحقة", "سابقة") & _ " بنفس رقم الفاتورة", vbExclamation Else ContrArr Col End If End Sub Private Function FndRow(facture As String, c As Long, r As Integer) As Long Dim tmp As Long, lastRow As Long lastRow = WS.Cells(WS.Rows.Count, 1).End(xlUp).Row If r = 1 Then For tmp = c + 1 To lastRow If WS.Cells(tmp, 1).Value = facture Then FndRow = tmp Exit Function End If Next tmp Else For tmp = c - 1 To 2 Step -1 If WS.Cells(tmp, 1).Value = facture Then FndRow = tmp Exit Function End If Next tmp End If FndRow = 0 End Function Private Sub ContrArr(tmp As Long) Dim n As Variant n = Array("TextBox7", "ComboBox1", "ComboBox2", "ComboBox3", "ComboBox4", _ "TextBox3", "TextBox4", "TextBox5", "TextBox6", "ComboBox5") If Me.TextBox8.Text = "" Then ClearControls Else Me.TextBox8.Tag = tmp For i = LBound(n) To UBound(n) Me.Controls(n(i)).Text = WS.Cells(tmp, i + 2).Value Next i tblUpdate tmp End If End Sub Private Sub SpinButton2_SpinDown() Navigation 1 End Sub Private Sub SpinButton2_SpinUp() Navigation -1 End Sub Private Sub résultats(facture As String) Dim Irow As Long Irow = ColRecherche(facture) If Irow = 0 Then MsgBox TextBox8.Value & " : " & "لا يوجد بيانات مطابقة لرقم الفاتورة", vbExclamation, "إنتـــباه" Me.TextBox8.Text = "" Label15.Caption = "السجل 1 من 1" Label15.Visible = False Else ContrArr Irow End If End Sub Private Function ColRecherche(facture As String) As Long Dim ColA As Range, cell As Range Set ColA = WS.Range("A2:A" & WS.Cells(WS.Rows.Count, 1).End(xlUp).Row) For Each cell In ColA If cell.Value = facture Then ColRecherche = cell.Row Exit Function End If Next cell ColRecherche = 0 End Function Private Sub ClearControls() Dim n As Variant n = Array("TextBox7", "ComboBox1", "ComboBox2", "ComboBox3", _ "ComboBox4", "TextBox3", "TextBox4", "TextBox5", "TextBox6", "ComboBox5") For i = LBound(n) To UBound(n) Me.Controls(n(i)).Text = "" Next i Me.TextBox8.Tag = "" Label15.Caption = "السجل 1 من 1" Label15.Visible = False End Sub Private Sub TextBox8_Change() If Me.TextBox8.Text = "" Then ClearControls Label15.Visible = False Exit Sub End If If Not IsNumeric(Me.TextBox8.Text) Then MsgBox "الرجاء إدخال قيمة رقمية فقط", vbExclamation Me.TextBox8.Text = "" ClearControls Exit Sub End If résultats Trim(TextBox8.Text) End Sub Private Sub tblUpdate(tblRow As Long) Dim facture As String, tblCount As Long, tmp As Long, lastRow As Long, tblMatch As Long facture = Trim(TextBox8.Text) lastRow = WS.Cells(WS.Rows.Count, 1).End(xlUp).Row tblMatch = 0: tblCount = 0 For tmp = 2 To lastRow If WS.Cells(tmp, 1).Value = facture Then tblCount = tblCount + 1 If tmp = tblRow Then tblMatch = tblCount End If End If Next tmp Label15.Caption = "السجل " & tblMatch & " من " & tblCount Label15.Visible = True End Sub لقد قمت برفع ملفين: الأول بدون إظهار عدد السجلات والثاني يقوم بإظهارها يمكنك اختيار ما يناسبك بالتوفيق........... البحث والتنقل.rar

If Not Intersect(Target, Me.Range("I:L")) Is Nothing Then UnitsArr = Array("فدان", "قيراط", "سهم", "م²") With srcWS lastRow = .Columns("I:L").Find(What:="*", _ SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row If lastRow < 10 Then Exit Sub ColArr = .Range("I10:L" & lastRow).Value ReDim tmp(1 To lastRow - 9, 1 To 1) For i = 1 To UBound(ColArr, 1) tbl = "" For j = 4 To 1 Step -1 If IsNumeric(ColArr(i, j)) And ColArr(i, j) > 0 Then tbl = tbl & IIf(tbl <> "", " و ", "") & ColArr(i, j) & " " & UnitsArr(4 - j) End If Next j tmp(i, 1) = tbl Next i مساحة2.xlsb

يكون أفضل لو أرفقت عينة لشكل البيانات المتوقع مع تحديد مكان وضعها هل في نفس مكان البيانات الأصلية أو في أعمدة مغايرة