سبد خرید
0

هیچ محصولی در سبد خرید نیست.

تمام دسته ها
  • تمام دسته ها
  • Power BI
  • Power Pivot
  • Power Query
  • ابزارها
  • افزونه ها
  • اکسل مدیا
  • توابع اکسل
  • دسته‌بندی نشده
  • دیده بان
  • گوگل شیت
  • مسائل کاربردی
  • معرفی کتاب
  • نمودار ها
  • وی بی - ماکرو

ایجاد حلقه های تکرار در VBA اکسل با ساختار For

حلقه ها در ماکرو
۵/۵ - (۴ امتیاز)

انجام کارهای تکراری در اکسل با حلقه ها در ماکرو

یکی از مهمترین مزایای استفاده از وی بی (VBA) در اکسل امکان تکرار کدها به تعداد دلخواه هست. این کار با استفاده از ساختارهایی به نام حلقه انجام پذیر هستند. در زبان VBA چندین نوع حلقه وجود دارد. در این آموزش قصد داریم ساختار دستوری حلقه For رو آموزش بدم. یکی از ساختارهای حلقه ها در ماکرو ، حلقه For برای اجرای کدهای نوشته شده به تعداد از پیش تعیین شده هست. یعنی فرض کنید شما قصد دارید نام شیت های فایل رو در سلول ها بنویسید. در این حالت مشخص است که به تعداد شیت های موجود باید کدها اجرا شوند.

حالت دیگر حلقه ها زمانی هست که شما از ابتدا دقیق نمیدانید که کدهای مدنظر شما چند بار باید تکرار شود و در واقع تکرار کدها مشروط به شرایط خاصی هست.

به صورت کلی حلقه For دارای دو ساختار هست:

  • ساختار For … Next
  • ساختار For … Each

ساختار For … Next

در این ساختار از یک متغیر استفاده میشه که مقدار آن در هر بار اجرا شدن کدهای درون حلقه For تغییر میکنه و زمانیکه به حد بالای مشخص شده برای این متغیر برسه از حلقه خارج میشه. به عنوان مثال در دستور زیر متغیر i از مقدار ۱ شروع و تا ۱۰ در هر بار چرخه تغییر میکنه (به عبارت دیگه دستور پاک کردن سلول ها ۱۰ بار تکرار میشه)

در این کد مقدار i در شروع حلقه از یک شروع میشه و سلول A1 پاک میشه و زمانیکه به انتهای حلقه میرسه با توجه به اینکه هنوز مقدار i از ۱۰ کمتر هست مجددا به ابتدای حلقه برمیگرده. اما در این حالت متغیر i مقدار دو خواهد داشت و سلول A2 پاک خواهد شد. این حلقه به همین صورت ادامه پیدا میکنه تا مقدار i به ۱۰ برسد و سلول A10 پاک شود. در این زمان مقدار متغیر i به عدد ۱۱ افزایش پیدا میکنه و با توجه به اینکه از عدد ۱۰ بیشتر است از حلقه خارج میشه و دیگه سلول A11 پاک نمیشه.

بعد از آشنایی با ساختار حلقه For، این سوال پیش میاد که اگر بخوایم مقداری که در انتهای هر حلقه، مقداری که به متغیر شمارنده اضافه میشه چیزی به غیر از یک باشه چه باید کرد؟

فرض کنید قصد ایجاد داده های محور افقی یک نمودار هستید. این اعداد از ۱ شروع و به فاصله ۰.۱ از هم زیاد میشه تا به ۱۰ برسه. برای این کار از کد زیر استفاده میکنیم:

در این کد با استفاده از دستور Step تعیین کردیم که متغیر i در انتهای حلقه به مقدار ۰.۵ زیاد بشه. همچنین از این متغیر استفاده کردیم که در هر بار چرخش مقدار i با استفاده از دستور Cells در سلول های ستون A نوشته بشه.

نکته:
برای اینکه چرخه از حالت افزایش (افزایش مقدار متغیر شمارنده) به حالت کاهشی تغییر کنه کافیه مقدار عددی که جلوی Step نوشته میشه عدد منفی باشه و مقدار اول و آخر باز جابجا بشه:
For i = 10 To 1 Step -1

 

نوع دیگری از حلقه ها در ماکرو که برای تعریف تکرار با شرط دلخواه وجود داره حلقه While هست که در مقاله ای دیگه بهش پرداخته میشه.

کلیدواژه : متوسط
133

من سامان چراغی هستم. دانش آموخته مقطع فوق لیسانس دانشگاه تربیت مدرس در رشته مهندسی صنایع. از سال 1388 اکسل و برنامه نویسی VBA رو به صورت حرفه ای شروع کردم.

دیدگاه کاربران
  • مهدیه ۲۹ آذر ۱۴۰۲ / ۱۱:۵۲ ب٫ظ

    سلام ممنون از مطلب مفیدتون
    من یک فرمول کسری رادیکالی توانی و یک x, y دارم یک حلقه لازم هست که فرمول حساب کنه تا به x,y بهینه برسه و این تکرار نامشخص انقدر ادامه بده که مثلا تو تکرار ۳۵ مقدار x,y با ،x,y تکرار ۳۶برابر بود اونجا متوقف بشه
    این بخش حلقه و شرط چطور مشخص کنم؟

    • آواتار
      حسنا خاکزاد ۱ دی ۱۴۰۲ / ۸:۱۰ ب٫ظ

      درود بر شما
      محاسبات حلقه ای یا باید از طریق VBA انجام بشه، یا اگر شدنی باشه (circular استفاده بشه)
      که فکر کنم با وی بی و گذاشتن شرط قبل از حلقه بهتر ج بگیرید

  • mahsat ۱۶ مهر ۱۴۰۲ / ۱۲:۲۵ ب٫ظ

    سلام وقت بخیر
    فرض کنید یک جدول دارید در یک ستون نام متقاضی هست و در ستون بعدی قیمت خرید و ستون بعدی تاریخ، که در ردیف های بعد هم میتونه اسم همون متقاضی تکرار شده باشه با قیمت جدید. میخواستم با vba یک بنویسم که اول اسامی رو مرتب کنه بعد جمع خریدش رو تو یه ستون جدید بما نشون بده. ممنون میشم راهنمایی بفرمایید.

    • آواتار
      حسنا خاکزاد ۱۶ مهر ۱۴۰۲ / ۶:۲۲ ب٫ظ

      درود بر شما
      برای مرتب کردن که دستور sort رو با ضبط ماکرو ببینید کدش چیه
      بعد هم برای جمع از تابع sumif میتونید استفاده کنید
      ولی بدون VBA راحت ترید!

  • پوریا ۳۰ مرداد ۱۴۰۲ / ۱۰:۵۹ ق٫ظ

    سلام وقتتون بخیر.من یه فرم طراحی کردم مثل فیش خقوقی که شماره پرسنلی وارد بشه اطلاعات میشینه رو فرم.میخواستم چنتا فرم را باهم چاپ کنم .مثال از شماره پرسنلی ۱ تا ۱۰ ولی حلقه FOR رو نمیتونم بنویسم لطف میکنین راهنماییم کنین

    • آواتار
      حسنا خاکزاد ۳۰ مرداد ۱۴۰۲ / ۱۰:۰۰ ب٫ظ

      درود بر شما
      جواب رو خودتون دادید
      استفاده از حلقه For
      اینجا هم فقط میشه سرنخ داد
      چون جزئیات زیاده، امکان ارائه کد نیس
      مگربصورت کلی …
      همون فرایند رینت رو متصل کنید به یک متغیر
      متغیر رو هم بذارید در حلقه
      که بتونه کار کنه

  • omid ۲۸ تیر ۱۴۰۲ / ۴:۴۱ ب٫ظ

    سلام وقت بخیر استاد
    خیلی ممنونم بابت اطلاعات دقیق و ارزشمندتون
    من میخواستم سلول a1 هر بار ۱عدد بهش اضافه بشه و هر بار شیت مورد نظر pdf بشه که اسم pdf در سلول A2 نوشته شده است.
    به طور مثال a1=1 هست و متن a2=امید هست در این زمان pdf من به اسم امید در دکستاپ ذخیره بشه و
    دوباره این اتفاق بیفته و به A1 یک عدد اضافه و به عدد ۲ تغییر پیدا کند که در این حالت A2 اسمش می شود بهنام و فایل مورد نظر به اسم بهنام ذخیره شود.
    سپاس

    • آواتار
      حسنا خاکزاد ۲۸ تیر ۱۴۰۲ / ۹:۴۰ ب٫ظ

      درود بر شما
      برای همچین چیزی نیاز به کدنویسی VBA دارید که شرط رو بیارید داخل کد و هر موقع انجتم شد، کد مربوط به پی دی اف کردن رو اجرا کنه

  • R ۴ بهمن ۱۴۰۱ / ۴:۴۶ ب٫ظ

    سلام
    من مدتیه می خوام یک ماکرو بنویسم توش گیر کردم. ممکمه برام بنویسید.
    می خوام یک شیت که شامل مثلا ۴ ستون است . در ستون اول a1 با بقه مقدر ستون a مقایسه کنه در صورت برابر بودن متلا ۱ با a 9 ستون دوم ایندو هم مقایسه بشه باز اگر برابر بود پ تمام ردیف را در شیت دیگر زیر هم بنویسه . ۰۹۱۱۸۳۶۱۵۱۹ شماره منه ممنون میشم راهنمای کنید

    • آواتار
      حسنا خاکزاد ۸ بهمن ۱۴۰۱ / ۴:۳۳ ب٫ظ

      درود بر شما
      میتونید این کار رو با حلقه و شرط if انجام بدید
      کافیه ساحتار حلقه رو بدونید
      یکی یکی چک کنه و بره سراغ بعدی

  • eliii ۶ آذر ۱۴۰۱ / ۳:۱۸ ب٫ظ

    سلام من یه فایل اکسل با رکوردهای تکراری متعدد دارم. میخوام رکوردهای تکراری رو به تعداد تفاوت مثلا سلول o و p حذف کنم، یعنی اگه از یه رکوردی ۱۵تا دارم و سلول o=15 و سلول p=5 باشه ۱۰=۵-۱۵ تا ازین رکورد تکراری حذف شه.
    Sub DelDups_OneList()
    Dim iListCount As Integer
    Dim iCtr As Integer

    ‘ Turn off screen updating to speed up macro.
    Application.ScreenUpdating = False

    ‘ Get count of records to search through.
    iListCount = Sheets(“Sheet1”).Range(“A1:A100”).Rows.Count
    Sheets(“Sheet1”).Range(“A1”).Select
    ‘ Loop until end of records.
    Do Until ActiveCell = “”
    ‘ Loop through records.
    For iCtr = 1 To iListCount
    ‘ Don’t compare against yourself.
    ‘ To specify a different column, change 1 to the column number.
    For iCtr = 1 To “O”-“p”
    If ActiveCell.Row Sheets(“Sheet1”).Cells(iCtr, 1).Row Then
    ‘ Do comparison of next record.
    If ActiveCell.Value = Sheets(“Sheet1”).Cells(iCtr, 1).Value Then
    ‘ If match is true then delete row.
    Sheets(“Sheet1”).Cells(iCtr, 1).Delete xlShiftUp
    ‘ Increment counter to account for deleted row.
    iCtr = iCtr + 1
    End If
    End If
    Next iCtr
    ‘ Go to next record.
    ActiveCell.Offset(1, 0).Select
    Loop
    Application.ScreenUpdating = True
    MsgBox “Done!”
    End Sub
    این کد Remove Duplicate عادی

    • سامان چراغی ۸ آذر ۱۴۰۱ / ۹:۳۹ ق٫ظ

      سلام، کد شما نیاز به اصلاح دارد.
      اول از همه اینکه منطق کد شما درست نیست:
      حلقه دوم که جهت بررسی سلول های تکراری هست با استفاده از حلقه Do تعریف شود و برای هر سلول تا زمانی تعداد ردیف های حذف شده از حداقل اختلاف دو سلول O و P و تعداد سلول های تکرار شده از آن سلول کمتر هست اقدام به حذف ردیف کند. لذا یک متغیر شمارنده دیگه نیاز دارید (که در کد شما از شماره ICtr به اشتباه در دو حلقه استفاده شده)
      یکسری ریزه کاری دیگه هم داره که در این کامنت نمیشه بیشتر توضیح داد.

    • علی ۷ فروردین ۱۴۰۲ / ۱۲:۲۱ ب٫ظ

      سلام وقت بخیر چطور میشه با استفاده از vba یک سلول اکسل رو مدام مورد بررسی قرار داد هر وقت که مقداری درون سلول قرار گرفت رنگ سلول بغلیش رو تغییر بده ممنون میشم راهنمایی بفرمایید

      • آواتار
        حسنا خاکزاد ۷ فروردین ۱۴۰۲ / ۲:۰۲ ب٫ظ

        درود
        با conditional foratting براحتی میشه اینکار و انجام داد
        اما اگر اصرار به VBA باشه
        باید کد if رو ترکیب کنید با رنگ پس زمینه interior color (جزئیات رو در ضبط ماکرو میبینید)
        بعد کد رو مثلا در selection change بنویسید که با هر تغییر اجرا بشه
        اما همچنان پیشنهادم conditional formatting هست

  • علی ۲۷ مرداد ۱۴۰۱ / ۶:۵۵ ب٫ظ

    با سلام من ۲ تا جدول در اکسس دارم جدول الف و جدول ب
    اطلاعات جدول الف جامع و کامله ولی اطلاعات جدول ب داری مغایرت با کویری در اکسس مغایرت ها رو پیدا کردم تعدادش زیاده و بخوام یکی یکی اصلاح کنم زمان بره راهی وجود داره دراکسس یا اکسل این مغایرت ها خودکار اصلاح بشه

    • آواتار
      حسنا خاکزاد ۲۹ مرداد ۱۴۰۱ / ۲:۳۰ ب٫ظ

      درود بر شما
      خیلی کلی گفتید
      نمیشه چیز خاصی گفت
      بصورت کلی بله اگر قاعده و منطقی باشه که بشه طبق اون درست کرد، میشه خودکارش کرد (بصورت کلی عرض کردم)

  • حامد محمدی ۲۵ تیر ۱۴۰۱ / ۹:۴۱ ب٫ظ

    سلام جناب مهندس چراغی
    وقت بخیر
    من ی vba دارم که میخوام ازش برای داده هایی که توی صفحه دیگه پر میشه دیتا بیس درست کنم.
    همه چیز خوب پیش میره فقط اینکه من میخوام ی خط دیگه به فرمول اضافه کنم اما خطا میده.

    For i = 8 To 127
    For j = 3 To 34
    If Sheet4.Cells(i, j) > 0 Then
    Sheets(“DB”).Select
    Sheet5.Range(“A5:G5”).Select
    Selection.Insert SHIFT:=xlDown
    Sheet5.Cells(5, 1) = Sheet1.Cells(13, 5)
    Sheet5.Cells(5, 2) = Sheet1.Cells(13, 3)
    Sheet5.Cells(5, 3) = Sheet4.Cells(i, 2)
    Sheet5.Cells(5, 4) = Sheet4.Cells(6, j)
    Sheet5.Cells(5, 5) = Sheet4.Cells(i, j)
    j = j + 1
    Sheet5.Cells(5, 6) = Sheet4.Cells(i, j)
    Else
    End If
    Next j
    Next i
    من میخوام به آخر فرمول این بخش رو اضافه کنم که ی سل دیگه هم به دیتابیس اضافه بشه
    j = j + 2
    Sheet5.Cells(5, 7) = Sheet4.Cells(i, j)
    نمیدونم باید متغیر دیگه تعریف کنم یا یک i و j دیگه؟؟؟
    ممنون میشم کمک کنید

    • سامان چراغی ۲۹ تیر ۱۴۰۱ / ۹:۰۲ ق٫ظ

      سلام
      چه خطایی میگیرید؟
      از F8 استفاده کنید که خط به خط کدها اجرا بشه و نشون بده که کجا داره خطا میده؟

  • علی کریمی ۱۷ تیر ۱۴۰۱ / ۱۱:۱۶ ب٫ظ

    با سلام خدمت شما
    چطور سطرهایی که سلول c4 خالیه رو حذف کنم و اونایی که پره بوردر کنم
    مثلا اینو تا آخرین سطر ادامه بدم
    sub macro1()
    If (Range(“c4”) = “”) Then
    Rows(“4:4”).Delete Shift:=xlUp
    End If
    End Sub
    ممنون میشم اگه راهنمایی کنید یا نحوه حلفه رو بگید

    • سامان چراغی ۲۳ تیر ۱۴۰۱ / ۹:۲۰ ق٫ظ

      سلام
      اگه منظورتون اینه که سطرهایی که ستون C آنها خالی هست حذف شود کافیه یک حلقه For ایجاد کنید (دقت کنید که دامنه این حلقه مهم هست و اگه درست تنظیم نشده باشه باعث کند شدن فایل شما بشه)

  • سجادیان ۲۶ دی ۱۴۰۰ / ۱۲:۴۶ ب٫ظ

    سلام من این کد رو دارم چطور میتونم به تعداد صفحات داخل اکتیو شیت م تکرار بشه؟ سوال دومم اینه که نام فایلم یه همچین چیزی باشه (cell(d,i+64).value=filename).ممنونم میشم پاسخ دهید
    Sub SaveAsPDFOptions()

    Dim saveLocation As String
    saveLocation = “C:\Users\1\Documents\myPDFFile.pdf”

    ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=[d1].Value, _
    OpenAfterPublish:=False, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    Quality:=xlQualityStandard, _
    From:=1, To:=1
    end sub

    • سامان چراغی ۲ بهمن ۱۴۰۰ / ۸:۴۵ ب٫ظ

      سلام
      میتونید یک حلقه For بنویسید که به تعداد (ActiveSheet.HPageBreaks.Count + 1) * (ActiveSheet.VPageBreaks.Count + 1) چرخش انجام دهد.
      در هر چرخش میتونید نام فایل رو با همین فرمولی که گفتید در یک متغیر متن ایجاد کنید و در هر دوره از حلقه به خصوصیت savelocation بدید.

  • مهدی ۲۷ آذر ۱۳۹۹ / ۱:۵۵ ب٫ظ

    سلام بنده میخوام با استفاده از کد نویسی در اکسل تعدادی شیت ایجاد کنم . چطور میشه این کار را انجام داد ؟
    آیا باید دستور ساخت شیت رو داحل یک حلق برد ؟
    اگر درسته لطفا راهمایی کنید که چگونه این کار را انجام بدهم

    • آواتار
      حسنا خاکزاد ۲۹ آذر ۱۳۹۹ / ۱۱:۲۸ ب٫ظ

      درود بر شما
      برای این کار ماکرو ضبط کنید و کد رو مشابه مقاله که ساختار for رو توضیح داده قرار بدید

  • سعیدرضا ۲۲ دی ۱۳۹۸ / ۱:۲۵ ب٫ظ

    با سلام
    من ۳۰ تا فایل به تعداد روز های ماه دارم که داخلش اطلاعات هست.
    حالا می خوام یک فایل جمع درس کنم که بره از داخل فایل های دیگه چنتا سلول مشخص رو بخونه بیاره داخل مجموع.
    چطور میتونم این کارو بکنم؟
    البته این سی تا فایل به مرور تهیه میشوند و باید فایل جمع هر فایل جدید که به فولدر ماه اضافه میشه رو اتوماتیک پیدا کنه و چنتا سلول را بخونه بیاره داره خودش.
    سپاس

    • آواتار
      حسنا خاکزاد ۲۲ دی ۱۳۹۸ / ۷:۴۱ ب٫ظ

      درود بر شما
      یا باید کدنویسی VBA انجام بدید یا با پاورکوئری انجام بدید

      • سعیدرضا ۲۳ دی ۱۳۹۸ / ۰:۵۶ ق٫ظ

        بسیار ممنون از پاسخ سریع تون
        بله بنده اطلاع داشتم هردو روش فوق چاره کار است اما چه کنم که هیچ کدام را بلد نیستم و فیلم ها اموزشی نیز کمکی نکرد. شاید دوستی بتواند به بنده کمک کند؟!
        سپاس از سایت بسیار پر بارتون

        • آواتار
          حسنا خاکزاد ۲۳ دی ۱۳۹۸ / ۱۲:۴۴ ب٫ظ

          سوالتون چیزی نیست که بشه اینجا مشخصا جواب داد. بستگی به خیلی چیزها داره
          اینجا فقط میشه راهنمایی کرد. مطابعه و یادگیری با خودتون

          دستور merge رو در پاورکوئری مطالعه کنید و روی داده هاتون اعمال کنید

  • شهاب ۲۵ آذر ۱۳۹۸ / ۱۱:۲۳ ب٫ظ

    سلام مشکل این کد چیه؟

    • سامان چراغی ۱ دی ۱۳۹۸ / ۱۰:۳۸ ق٫ظ

      سلام
      کد زیر رو با اکد انتهای پیام جابجا کنید:

      کد صحیح:

  • علی ۱۷ مرداد ۱۳۹۸ / ۱۲:۲۲ ب٫ظ

    من یک ستون دارم با یه سری داده های تکراری
    در مقابل این ستون یه ستون دیگه هسات که شامل یه سری عدد هست
    حالا میخوام بنویسم تا جایی که ستون اول با هم مساوی هستند دادهای ستون دوم رو کنار هم (جمع نکنه) بذاره
    Sub ali()
    Dim i As Integer
    Dim j As Integer
    j = i – 1
    For i = 2 To 6
    If Cells(i, “a”).Value = Cells(i – 1, “a”).Value Then
    Cells(i – 1, “c”) = Cells(i – 1, “b”).Value & Cells(i, “b”).Value
    End If
    Next i

    End Sub
    این کد رو نوشتم اما این فقط دو ستون رو کنار هم میذاره
    برا نوشتن حلقه تو این دستور کمک میخواستم ازتون
    ممنون

    • سامان چراغی ۱۲ آبان ۱۳۹۸ / ۹:۲۰ ق٫ظ

      سلام
      برای حل این مسئله نیاز به تعریف حداقل دو حلقه تو در تو هست برای همین درون حلقه For اولی که خودتون نوشتید باید یک حلقه For دیگه تعریف کنید که بر اساس عددی که در ستون اول بررسی میکنید داده های ستون دوم (با استفاده از حلقه دوم) بررسی کنه و نتیجه دلخواه رو براتون ثبت کنه.

  • میثم ۶ تیر ۱۳۹۸ / ۸:۳۶ ق٫ظ

    سلام برای تبدیل یک کد دستوری به یک حلقه چکاری باید انجام دهیم
    مثلا کد
    Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    If (KeyAscii > 47 And KeyAscii < 58) Then
    KeyAscii = KeyAscii
    Else
    KeyAscii = 0
    If Len(TextBox1.Text) < 10 Then
    MsgBox "شما نمی‌توانید حروف وارد کنید ، فقط باید عدد وارد کنید", vbMsgBoxRight, "اطلاعات ورودی اشتباه است"
    Cancel = True
    End If
    End If
    End Sub
    را چطور حلقه کنم که بتونم برای چندین تکس باکس استفاده کنم

    • سامان چراغی ۱۱ تیر ۱۳۹۸ / ۹:۱۳ ق٫ظ

      سلام، از حلقه ها برای انجام این کار استفاده کنید.

  • سلیمانی ۲۸ تیر ۱۳۹۷ / ۹:۰۹ ق٫ظ

    سلام
    من این مقاله شما را خوندم . خیلی جالبه با vba خیل از حلقه های مورد نیاز را می تونم ایجاد کنم ولی مشکلی که دارم اینه که چطور وارد محیط vba بشم تا حلقه را ایجاد کنم. ممنون میشم اگه راهنمایی کنید.

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

توسط
تومان