The For Next and For Each Loops Explained for VBA & Excel

Bottom line: The For Next Loops are some of the most powerful VBA macro coding techniques for automating common tasks in Excel.  This article explains how the loop works to repeat actions on a collection of items, which saves us a ton of time with our jobs.

Skill level: Intermediate

For Next Code Loop Explained for VBA Excel

The Power of VBA Loops

In Excel we spend a lot of time repeating simple tasks.  This could be actions like: formatting multiple ranges, unhiding multiple sheets, copying and pasting to several workbooks, apply filters to multiple tables or pivot tables, replacing values, updating formulas, etc.

Can you think of a few tasks where you had to repeat the same process over and over again?

These tasks can be extremely time consuming and BORING!

Repetitive Excel Tasks Get Boring - VBA Loop

Fortunately, there is a way out.  We can use loops in our VBA macros to repeat actions very quickly.  Tasks that can take hours to do manually can be completed in a matter of seconds with a loop.

The For Next Loop is the most common type of loop that helps us with these repetitive jobs.  In this article we will look at the two types of For Next Loops.

Download the Example File

Download the free Excel file that contains examples of macros with the For Next Loop.

How Does the For Next Loop Work?

The For Next Loop allows us to loop through a collection of items in Excel.  The collection can be a collection of objects or a list of numbers.

Examples of collections of objects include:

  • Cells in a range.
  • Worksheets in a workbook.
  • Open workbooks on the computer.
  • Pivot tables in a worksheet.
  • Pivot fields in a pivot table.
  • Shapes on a worksheet.
  • And any other object you interact with in Excel.

The job of the For Next Loop is to perform the same actions (lines of code) on each item in the collection.

How a For Next Loop Works in VBA

The example below contains a For Next Loop that loops through each worksheet in the workbook and unhides each sheet.  The loop starts at the first item in the collection (the first sheet in the workbook), and performs the line(s) of code between the For and Next lines for each item in the collection (every sheet in the workbook).

Sub Unhide_Multiple_Sheets()

Dim ws As Worksheet
    
    For Each ws In ActiveWorkbook.Worksheets
        ws.Visible = xlSheetVisible
    Next ws

End Sub

Of course we can use logical statements like If statements to test properties and conditions before performing the actions.  The following macro only unhides sheets that have the phrase “ABC Global Co.” in cell A1 of each sheet, and hides all other sheets.

Sub Unhide_Report_Sheets()

Dim ws As Worksheet
    
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Range("A1").Value = "ABC Global Co." Then
            ws.Visible = xlSheetVisible
        Else
            ws.Visible = xlSheetHidden
        End If
    Next ws

End Sub

The Two Types of For Next Loops

There are really two types of For Next Loops.

  1. For Each Next Loops loop through a collection of items.
  2. For Next Loops loop through a set of numbers.

Let's take a look at how each works.

The For Each Next Loop: Loops Through a Collection of Items

As we saw above, the The For Each Next Loop allows us to loop through a collection of items or objects.  This is probably the most common loop we use in Excel because we are working with collections of objects.  Again, these collections are the cells in a range, worksheets in a workbook, pivot tables in a worksheet, etc.

For Each Next Loop Loops Through All Items Worksheets in a Collection

We will use the example of writing a For Each Next Loop to loop through all the worksheets in a workbook.

The For Each Next Loop Explained for Excel VBA

There are 4 basic steps to writing a For Each Next Loop in VBA:

  1. Declare a variable for an object.
  2. Write the For Each Line with the variable and collection references.
  3. Add line(s) of code to repeat for each item in the collection.
  4. Write the Next line to close the loop.

Let's take a look at each of these steps in detail.

Step 1 – Declare a Variable for an Object

We first need to declare a variable that will temporarily store a reference to the object.

The Dim line at the top of the macro declares a variable as an object.  In this case the object is a worksheet.  We can create any variable name we want as long as it is not the same as another reference in VBA.  “ws” is the most common variable name for a worksheet object, but you can change this.

Dim ws As Worksheet

Step 2 – The For Each Line

Next we will write the For Each statement.  This is the first line of code in the loop.

For Each ws In ActiveWorkbook.Worksheets

The first two words are For Each.  Then we type the variable name, followed by the word In  Finally, we specify where the collection exists.  In this case we want to loop through all the worksheets in the ActiveWorkbook.  So, we type ActiveWorkbook.Worksheets.  That line references all the worksheets in the ActiveWorkbook.

If you want to loop through worksheets of a specific workbook, then you can use the Workbooks property to reference that workbook by name.

For Each ws In Workbooks("Book2.xlsx").Worksheets

Just remember that the workbook you reference has to be open before the For Next line of code runs.  Of course, we can use the Workbooks.Open method to open the workbook as well.

Step 3 – Add Code to Repeat for Each Iteration

After the For Each line, we add the line(s) of code that will be performed on each sheet.  In this example we just have one line of code that unhides the sheet.

ws.Visible = xlSheetVisible

In this line of code we are using the ws variable to reference the current worksheet in the loop.  When the loop runs, it sets a temporary reference to the ws variable for each iteration in the loop.

This would be the same as if we were to set the ws variable to a specific sheet using the following line of code.

Set ws = Worksheets(1)

However, we do NOT need this line with the For Each Next Loop.  The loop takes care of setting the variable for us for each iteration in the loop.

For the first iteration in the loop, the ws is set to Worksheets(1).  In the next iteration, ws is set to Worksheets(2).  This continues as the loop iterates through all sheets in the workbook.  This is very powerful because we can reuse the variable to reference the worksheet several times within the the loop.

Step 4 – The Next Line Loops Back

The final line of code in the loop is the Next line.

Next ws

When the macro hits this line of code it does two things:

  1. First, it changes the variable reference to the next item in the collection.  In this example, the ws variable is changed to reference the next sheet in the workbook.
  2. Second, it loops back up to the run the line of code directly below the For Each line.  It then runs all the lines of code between the For Each and Next lines in top to bottom order.
What Happens When the Next Line Runs in a Loop Excel VBA

When the last item in the collection (worksheet in the workbook) is reached, the looping stops, and the macro continues on to the next line of code below the Next line.

What Order Does the For Each Loop Run In?

The For Each Loop always starts at the first item in the collection and loops through to the last item in the order they appear in Excel.  This is based on the index number of the items in the collection.  Here are some examples of the order the loop runs in for common objects.

  • Worksheets: Starts at the first sheet tab in the workbook and loops to the last in the order the tabs are displayed in the workbook.
  • Workbooks: Starts at the first workbook that was opened and loops in the order that workbooks were opened.  Workbooks are assigned an index number as they are opened.
  • Cells: Loops left-to-right, then down.  Starts in the first cell in the range and loops to the next column in the same row, then goes to the next row.
  • Tables & Pivot Tables: Starts with the first object that was create in the sheet and loops in the order the objects were created.  This same principle is true for other objects you create in sheets like shapes, charts, slicers, etc.

Items/Objects Are NOT Selected in the Loop

It's important to note that when we loop through a collection of objects, each object is NOT selected in Excel.

Loops Do NOT Select Each Item or Object

The loop creates a reference to the item/object with the variable.  The variable is temporarily set to a reference of the object.  The object is NOT selected and does NOT necessarily become the active object.  To select the object we can use the Select or Activate methods.  You would also have to make sure the objects parent object(s) are selected first.  Checkout my article on the Excel Object Model and Object Hierarchy in VBA for more on this.

The Next Loop: Loops Through a Set of Numbers

We can also use the For Next Loop to loop through a set of numbers.  This can be useful when we are looping through userform controls, arrays, or if we want to loop through a collection backwards.
For Next Loop Code Explained Excel VBA Macro
The basic operation of the For Next Loop is the same as the For Each Loop.  The difference is the format of the For line.

Step 1 – Declare a Variable for a Number

To loop through a set of numbers we first need to declare a variable to a whole number data type.  We can use Integer or Long integer.

Dim i As Long

The variable is referred to as the Counter because it increments or counts up/down for each iteration in the loop.

A side note on Long: The Long (integer) data type holds a bigger number than Integer.  It takes up more memory, but today's computer have so much memory that it's no longer a problem.  We can use Long variables all the time.  The letter L looks like the number 1 in VBA, so I'm now using i as the variable name even though I use Long as the data type.  This is all a matter of personal preference and you can name the variable whatever you want.

Step 2 – Write the For Statement

Next we write the For statement.  The basic construct is the keyword For, followed by the variable name (counter), then equals sign, start value To end value.

For i = 1 To 10

The start and end values can be referenced as numbers, or we can use integer/long variables in their place.

For i = iStart To iEnd

We can also use properties of objects that return a number.

For i = 1 To ActiveWorkbook.Worksheets.Count

That line of code would return the number of sheets in the active workbook.  However, it is NOT looping through each worksheet.  The loop is just looping through a set of numbers.  We have to create a reference to a worksheet with the counter variable (i) as the index number of the Worksheets property.  Step 3 shows this reference.

Step 3 – Add Code that Repeats for Each Iteration

The rest of the loop functions the same as the For Each loop.  We can add lines between the For and Next lines that will run for each iteration of the loop.  The counter variable can be used multiple times in these lines of code.

Worksheets(i).Visible = True

Step 4 – The Next Line Increments the Number & Loops Back

Finally, we add the Next line at the bottom.

Next i

When the macro runs it will set the variable equal to the first number in the For line.  When the macro hits the Next line, it will add 1 to the value of the variable, or count up.  So, i = 2 in the second iteration of the loop.  It continues to loop until the last number in the loop is reached.

By default, 1 is added to the variable counter for each iteration in the loop.  This is called the Step Value, and we can control the value of each step in the counter.  The Step value is added to the end of the For line.  The following line will add 2 to the counter for each iteration in the loop.

For i = 2 To 20 Step 2

If you wanted to shade every other row in a sheet, you might use a loop like this.

For Next Loop Step Counter Banded Rows Example Excel VBA

Looping Backwards

We can also use the Step Value to loop backwards by specifying a negative number.

For i = 100 To 1 Step -1

Notice that the Start Value is now the larger number and the End Value is the smaller number.  The loops starts at 100 (Start Value) and subtracts 1 from the counter variable (Step -1) for each iteration in the loop until it gets to 1 (End Value).

The Step keyword is optional.  If you do not specify it then VBA assumes a Step value of 1.

Looping backwards is great if you are deleting items.  I will write a separate post on this, but the general idea is that when we are looping through a collection and deleting items, the size of the collection gets smaller as items are deleted.  The loop will typically hit an error once it gets to the 10th item, when there are now only 9 items in the collection.  Looping backwards prevents this potential error.

Exiting the Loop Early

Typically the loop will iterate through all items in the collection, then continue on to the next line of code below the Next line.  However, we can stop the loop early with an Exit For statement.

Exit For

The following macro uses the Exit For statement to exit the loop after the first sheet that starts with the word “Report” is found an unhidden.

Sub Unhide_First_Sheet_Exit_For()
'Unhides the first sheet that contain a specific phrase
'in the sheet name, then exits the loop.

Dim ws As Worksheet
    
    For Each ws In ActiveWorkbook.Worksheets
    
        'Find the sheet that starts with the word "Report"
        If Left(ws.Name, 6) = "Report" Then
            
            ws.Visible = xlSheetVisible
            
            'Exit the loop after the first sheet is found
            Exit For
        
        End If
    Next ws

End Sub

The ws variable retains the reference to the worksheet after the loop is exited early, and can be used again in the code below the loop.

Variable Reference Is Not Reset When VBA For Next Loop Exit Early

Variable Not Required After Next Keyword

You might have noticed that I added the variable after the Next keyword at the bottom of the loop in the examples above.

Next ws
Next i

This is NOT required, and you might not see it in other examples you find on the web. However, I like to include the variable after Next for two reasons.

  1. We can use it when debugging code to see the value of the variable by hovering the mouse over the variable when the code is stopped.
  2. It makes it easier to understand which For line the Next line is connected to. This is especially true when you have multiple loops or nested loops in your macros.
Variable After Next is Not Required in For Next Loop

Therefore, I recommend adding the variable after the Next keyword as a best practice.  A little extra work up front will save time & headache in the future.  Trust me! 😉

Macro Code Examples of VBA For Loops

Here are additional articles with macros that use at least one For Next Loop.

3 Ways to Unhide Multiple Sheets in Excel + VBA Tutorial

Automatic Default Number Formatting in Excel Pivot Tables

3 Tips to Save and Close All Open Excel Workbook Files + Macro

The SUBTOTAL Metrics Macro – Create a Summary Table of All Function Types

How to Add a Table of Contents Image Gallery Sheet to Your Excel Files

Hide & Unhide (Filter) Columns with a Slicer or Filter Drop-down Menu

Filter a Pivot Table or Slicer for the Most Recent Date or Period

How to Make Your Excel Dashboards Resize for Different Screen Sizes

Convert Pivot Table to SUMIFS Formulas + Free VBA Macro

VBA Macro to Hide All Columns That Contain a Value in a Cell

How to Repeat Tasks with VBA Code – Looping – Great article with lots of examples from my friend Chris Newman at The Spreadsheet Guru.

What Task Do You Want To Loop?

I hope that article helps get you started with loops.  Don't forget to download the free Excel file that contains the code samples.

Loops are definitely an intermediate coding technique that force us to go beyond the macro recorder.  Unfortunately, the macro recorded cannot create loops.  However, this is a skill that you will be able to use over and over again throughout your career to automate simple and complex tasks.  Understanding how to use loops will give you magical powers with Excel.

Please leave a comment below with a task that you want to automate with a loop.  Thank you! 🙂

51 comments

Your email address will not be published. Required fields are marked *

  • Wow such great article.thanks for writting this article and hope will get more article on VBA. Thanks

  • Hi Jon

    Thank you so much.

    Do you have please some courses on VBA for completly beginners? I would like learn VBA from scrach.

    Regards,

    Ray Passave

  • Jon, great tutorial as usual!
    One task I’ve always wanted to automate was the conversion of fields added to my pivot table from SUM to AVG (I deal with a lot of percentages). I find I have to select each field (using right-click in the table) to “Summarize Values By…”, choose AVG, modify the field name (get rid of “Average…” and add a space at the end), then set the Number Value to Percent (with zero decimals), OK, OK., and do this for each field (sometimes there are many!). I knew I couldn’t record a macro for this since it loops, but this tutorial gives me hope I can do it in VBA! If you have a suggestion for this, it would save me eons of time (well maybe not that much!). Keep up the great Excel work!

    • Great question Red! That definitely sounds like one of those boring repetitive tasks. 😉

      Yes, we can absolutely use a For Each Next loop to loop through the DataFields in a pivot table. The DataFields are the fields that are in the Values area of a pivot. Here is a macro that will change every field in the Values area of the selected pivot to COUNT.

      Sub Change_PivotField_Function()
      'Changes the function for each field in the values area
      'of the selected pivot table.

      Dim pt As PivotTable
      Dim pf As PivotField

          'Create a reference to the selected pivot table
          Set pt = ActiveCell.PivotTable
          
          'Alternate way to reference a pivot table
          'Set pt = Worksheets("Sheet1").PivotTables("PivotTable1")

          For Each pf In pt.DataFields
              pf.Function = xlAverage
              pf.Calculation = xlNoAdditionalCalculation
              pf.Name = pf.SourceName & " "
              pf.NumberFormat = "0%"
          Next pf

      End Sub

      There are a few properties of the pivotfield that we need to set.

      The Function is the Summarize Values By… property. Here is a list of all function enumerations on MSDN.

      The Calculation property is used for the Show Values As… property. If this has been set then you can change it to xlNoAdditionalCalculation to remove the calculation. Here is a list of all calculation enumerations on MSDN.

      To change the name of the field we can set the Name property to the SourceName and concatenate a space at the end. The SourceName is the name of the field in the source data.

      Finally, we can use some code from last week’s post on Automatic Pivot Table Number Formatting to change the NumberFormat property to a percentage format.

      I hope that helps. I’ll write up a more detailed article in the future on this. Thanks!

      • Jon,
        I created a macro with this code and it worked beautifully! However I’ve hit an issue with storing this macro for use on future files. The best technique I saw was to save the file as an XLAM in the AddIns folder (then I made a ribbon item to invoke it). This worked but Excel through a security warning each time I opened a file that required me to hit the Enable button. I adjusted the Trusted settings slightly (and have since undone them) but now the macro will not run (“Unable to get the PivotTable property of the Range class”), and I still have the security warning. Any advice? Thanks again!

  • This is what I’m looking for VBA LOOP and that’s automation begins.
    Thanks will follow closely.

  • Hi Jon, this is very helpful! I have question: can For Next Loop use for multiple workbook? I have folder content multiple excel workbook, I need to run a macros to lock some columns for each workbook (each workbook only have one worksheet). thanks!

    • Hi Jen,

      Great question! Yes, this is definitely possible. There are a few options for looping through workbooks in a folder. My friend Chris has a good article with an example of how to loop through all workbooks in a folder. His approach allows you to browse for the folder, or specify it, then the macro will find all the files in a workbook. You can then add code to run on each workbook.

      If you have all the files open, then you can use a more simple loop to loop through the open workbooks. Here is an example.

      Sub Loop_Open_Workbooks()

      Dim wb As Workbook

          For Each wb In Application.Workbooks
              If Left(wb.Name, 7) = "Report" Then
                  'Add code here to perform on the workbook.
              End If
          Next wb
          
      End Sub

      That macro will run the code on the workbooks where the file name starts with Report. You can modify the Left function to match a naming convention for your files. This prevents the macro from being run on any files you have open accidentally or any files open in the background like the Personal Macro Workbook.

      Another solution is to create a list in a worksheet of all the full file paths to each file. Then write a macro that loops through the cells in the list and uses the Workbooks.Open method to open each file. This solution works well if you have files in multiple folders, or you only want to run the code on specific files in the folder.

      I teach this solution in module 11 of my VBA Pro Course. In that module we build an application I call the File Manager. This is a VBA application that allows you to run any macro on a set of files. Here’s a link to a video preview of module 11.

      I hope that helps get you started. Thanks again!

      • Thank you so much! Jon, very useful instruction! Also, I am very interesting in your file manager application, I saw the overview video, what about the rest? thanks!

  • Thanks for the great article. I have been trying to understand how FOR EACH loop works and the use of ws variable.

    Would it be a good practice to clear the variable set WS = nothing after the loop if we are planning to use variable again in the code?

    • Hi Roman,

      Great question! Once the For Next Loop completes, the final iteration on the Next line will attempt to set the variable to the next item in the collection.

      With the For Each Loop, there are no more items in the collection. So, the variable will be set to Nothing. You can test this by adding a break point to a line of code below the loop, then hover over the variable to see that it is set to nothing. Here is a screenshot.

      For Each Next Loop Variable Set to Nothing After Loop

      There are some caveats to this.

      I forgot to mention in the article that we can use the Exit For line to exit the loop early. You might use this once a condition is met, and you no longer need to continue the loop. In this case the variable will still be set to the last object it was set to in the loop.

      So, if you are using an Exit For statement in your loop, you might want to reset the variable below the loop. You can also just set ws to the new worksheet reference. You don’t necessarily have to set it to nothing first.

      The For Next Loop (for looping through numbers) is a little different. After the final iteration in the loop, the Next line still adds the Step value amount to the Counter variable. In the screenshot below you can see that the value of i is 11, even though the End Value is 10.

      For Next Loop Variable Increments by Step Value After Loop VBA Excel

      In this case you might want to reset the variable back to the End Value, if that number is useful later on in the macro. I can’t think of a time when I actually needed to do this, but it’s good to know how it works. I hope that helps. Thanks for the question! 🙂

  • What I am trying to do is set up a macro to hide columns and set filters which I would store in a table for each worksheet I want to accomplish this on. The tables vary in length and would be able to be changed by the user. Would a For Next loop be possible for this?

    • Hi Jerry,
      Yes, you would probably use multiple For Next Loops for that project. Here is an article I have on how to hide columns that contains a specific value.

      You can also reference multiple columns in the same range reference, and then hide them all at one time. You can store strings of text with the range references, so you don’t have to loop.

      Range("B:C,D:D,J:L").EntireColumn.Hidden = True

      I hope that helps get you started. Thanks!

  • Hello Jon,

    Thank you so much for posting such a useful and easy to understand article. I will definitely use this skill in my daily work activities.

  • I would like to automate changing links. We use spreadsheets with a lot of links to monthly worksheets. For example we have ‘lmpsheet 0617.xlsb’, ‘lmpsheet 0717.xlsb’, ‘poolA 0617.xlsb’, ‘poolA 0717.xlsb’, etc. Each month we copy the previous month’s file and name it for the new month, so’poolA 0717.xlsb’ is created from ‘poolA 0617.xlsb’. Then we have to update all of the links to the new month’s files.

    For some of the links the new file is in the same folder as the old file. For some the folder changes, but in a predictable way (by month). I would like to create a macro that will open a list of files, change the links, recalculate, and save and close the file.

    With the help of the internet I created a file that contains macros that will open, update the links, recalc, save, and close a list of files. My team loves it. They each have their own button and can update the list if needed.

    I have been slowly working my way through your VBA Pro course. It has been great.

  • Dear Jon,

    Thanks again for your contribution. I am quite familiar with the For …Next and For Each … Next loops (as well as with the Do While … Loop and Do Until … Loop).

    In the For … Next and For Each … Next loops, I only reference the object variable or counter variable in the Next statement, when I use embedded loops.
    I do that that for code clarity and readability.

    Is it bad habit or even bad programming?

    • Hi Edil,

      I believe this is really a matter of personal preference. I added a section to the end of the article above with the heading “Variable Not Required After Next Keyword”. I’m not sure if you saw this. It’s a long article… 🙂

      I give two reasons there, but again it’s just a matter of preference. It’s a habit I’ve gotten into, but honestly I break my own rules sometimes if I’m in a hurry to create a quick macro.

      It’s probably best to just consider you audience, and who might be reading your code or maintaining it in the future. If it’s a project you are going to hand off at some point, then try to make it as easy as possible to read. It will also help when you come back to the macro a year later and have to figure out what you did… 🙂

      I hope that helps.

  • Hi Jon,
    I have been looking at your Next Loop post as I am trying to create a macro to rename all the sheets in a workbook taking cell A2 on each sheet as containing the name for that sheet, but I can’t quite get it right. I wondered if you could get me some help on the code for this as it take along time to do manually when there are a lot of sheets in the workbook.

  • Jon, Thanks for the great info. I am trying to figure out how to use VBA to do multiple find and replace operations. My data identifies everything with a code but when I present the data I need to change the code to a human name and the code ie 8654 needs to be 8654 Springfield. there are about 50 of these codes that need to have the human attached.
    What is your suggestion on how to do a multiple find and replace?
    Thanks

  • I normally received a mail from about 150 people on weekly basis with an excel attachment (10X Leads.xlsx) via my Gmail account configure with Ms Outlook. I require your assistant for VBA script that I can run to automate the downloading of the files into a folder (10XLeads) and marge the content of each files into a single workbook (M10XLeads.xlsx) and delete other files after merging.
    Thanks in anticipation for your kind gesture and I await your positive response.

  • Hi Jon,
    I have a data set that uses an * before some dates when there are duplicate entries for another column item. I’m trying to automate a clean up that will remove the * from the date fields. I have a function that works to remove *, but I’m struggling with a procedure that would loop through the column, after the header, call the function, and update the cell after removal of the *. Any advice?

  • Thanks Jon for taking the time to write this article. It really explained the For…Next loop in a way that I could easily understand.

  • hi
    I’m writing this code to match a text in a range of cell
    for each c in xlBook.Sheets(“Sheet1”). Range(“I7:I54”)
    if c.value = “A” then
    msgbox c.address
    end if
    next
    but if I want to show the first cell or a specific one
    in messagebox so how it could be.
    thanks

  • I have to prepare the dashboard file consolidation in this file which is they providing in that file contain around 10 sheets. i have to consolidate two sheets only. if incase any sheet missed i have to exclude that sheet. remain one sheet i need to create.
    basic this requirement i need to prepare the VBA codes. I have coding for consolidation, but i need to run the ” Do while loop ” coding for if sheet not available need to consolidate all those file in folder file availability.

    • Thanks for your helpful post. Over time, I have come to be able to understand that the symptoms of mesothelioma are caused by the particular build up connected fluid between your lining on the lung and the upper body cavity. The ailment may start while in the chest area and multiply to other parts of the body. Other symptoms of pleural mesothelioma cancer include losing weight, severe breathing in trouble, fever, difficulty ingesting, and bloating of the neck and face areas. It ought to be noted that some people with the disease don’t experience almost any serious indications at all.

  • Hi, I a really good looking way to explained, thank you. would be nice to connect your job with pseudocodes and flowcharts.

  • Hello Jon,

    Great article. I see I am coming to this a bit late in the game, so to speak.
    I have a huge spreadsheet at work, A through AD, and about 82000 rows.

    This spreadsheet is run basically once a week, and I have a ton of repetitive tasks I have automated.

    The next one I am trying to automate has me a bit perplexed.

    Any suggestions would be greatly appreciated.

    Column A is Parent and Column V is Bill Type.

    (As an explanation, the Parent is a number, identified as ParentID in code, assigned to the matter. The Bill Type will be either Ebill, Email, or Paper.)

    When a new matter is assigned, the Bill Type is left blank.

    What we then have to do is look at the ParentID for the blank Bill Type, and figure out which bill type is used the most for that ParentID throughout the spreadsheet. We will then assign the most used bill type to the ‘blank’ one. I hope that made sense.

    So, in short, I have attempted to write code to look through column V until it finds a blank, and then set the ParentID variable to whatever the is in the Parent column of the row containing the blank.

    I then have attempted to have it go through the spreadsheet, and for every ParentID (of the first blank), it will count Ebill, Email, and Paper (in column V).

    I then need it to assign the Bill Type with the highest count to the first blank.

    Then it will go back to the start, and find the next blank…rinse and repeat.

    I have spent hours on this already, and I am at a stop, as what I have done is not working, and I am unsure of what approach would be best.

    Please advise, if you can.

    Thank you for your consideration.
    Jason

  • My query is as follows
    I am having frequency of 7days 14 days 21 days 45 days and 90 days in one column and a date in another coloum respectively

    I want to add the number of days as per the frequency mentioned against the date and need formula for the same is it possible? The list is having more than 1000 lines

  • My Query When i am trying to Ex: Sheet1 move or Copy, Move to end and create copy. Trying to this VBA macro using do while Loop. We need multiple times repeat the same sheet but i am unable to crack it. Can you please help on this……

  • Query for excel wont save if specific cell is empty, then when you use the code to 10 cells but you only encode into 9 but will still save and put (N/A) to 10th cell..is it posible?

  • HI,
    i would like to ask, can we use step in for each? like
    for i = 1 to 20 step 2
    =>> for each i in Range step 2
    or kind of. thanks

    • Hi, it is not possible to do what you are asking, but I’m sure there must be other way to get to the same result.

  • sample image >>>
    https://i.imgur.com/OGefy8Q.jpg

    code vba excel >>>>>

    ub pasteoneonecolumn()
    Dim lngRow As Long, objCOMAddin As COMAddIn
    lngRow = 1
    With ActiveSheet
    For Each objCOMAddin In Application.COMAddIns
    .Cells(lngRow, “E”).Value = .Cells(lngRow, “A”).Value
    .Cells(lngRow, “F”).Value = .Cells(lngRow, “B”).Value
    .Cells(lngRow, “G”).Value = .Cells(lngRow, “C”).Value
    lngRow = lngRow + 1
    Next objCOMAddin
    End With
    End Sub

    for ask >>>>

    why not all, only A1:C7
    only up to 7 columns, how can I get all the columns?
    anyone can help for vba excel that ?

    for colomn E:G copy paste, for function, same with Sub pasteoneonecolumn(), because im try only work A1 to C7, Do you have a solution?

  • Greetings.

    I’m trying to figure out if I can make a loop that
    Copies value in last cell
    Pastes value in next cell
    Does this for 35 total cells.

    IE If the last cell in a column says: 5
    I want the loop to be able to copy that value, 5 and paste it into the next cell in the column
    And continue that process until 5 is listed 35 times.

    But then be able to to put the number 6 in the next cell of that column and have the code run once more, copying the number 6 and pasting it 35 times down the column.

    Is this even possible?

  • Hello. Im a project engineer from Brazil. I have a repetitive task in excel. Can you help me?
    The task is: i want to repeat this cels

    19700.ENG.CONSULT 0
    19700.ENG.SENIOR 0
    19700.ENG.PLENO 0
    19700.ENG.JR 0
    19700.PROJ.SEN 0
    19700.PROJ.PLEN 0
    19700.PROJ.JR 0
    19700.TEC.PLAN 0
    19700.TEC.ISP 0
    19700.DES.2D 0
    19700.ENG.CON.HE1 0
    19700.ENG.S.HE 0
    19700.ENG.P.HE 0
    19700.ENG.JR.HE 0
    19700.PROJ.S.HE 0
    19700.PROJ.P.HE 0
    19700.PROJ.JR.HE 0
    19700.TEC.PLAN.HE 0
    19700.TEC.INSP.HE 0
    19700.DES.2D.HE 0
    19700.ENG.CON.DOM 0
    19700.ENG.S.DOM 0
    19700.ENG.P.DOM 0
    19700.ENG.JR.DOM 0
    19700.PROJ.S.DOM 0
    19700.PROJ.P.DOM 0
    19700.PROJ.JR.DOM 0
    19700.TEC.PLAN.DOM 0
    19700.TEC.INSP.DOM 0
    19700.DES.2D.DOM 0

    in this sequence:

    51201
    5120101
    512010101
    512010102
    512010103
    5120102
    5120103
    5120104
    5120105
    5120106
    5120107

    And so the macro give me something like this for all the sequence above:

    51201 19700.ENG.CONSULT 0
    51201 19700.ENG.SENIOR 0
    51201 19700.ENG.PLENO 0
    51201 19700.ENG.JR 0
    51201 19700.PROJ.SEN 0
    51201 19700.PROJ.PLEN 0
    51201 19700.PROJ.JR 0
    51201 19700.TEC.PLAN 0
    51201 19700.TEC.ISP 0
    51201 19700.DES.2D 0
    51201 19700.ENG.CON.HE1 0
    51201 19700.ENG.S.HE 0
    51201 19700.ENG.P.HE 0
    51201 19700.ENG.JR.HE 0
    51201 19700.PROJ.S.HE 0
    51201 19700.PROJ.P.HE 0
    51201 19700.PROJ.JR.HE 0
    51201 19700.TEC.PLAN.HE 0
    51201 19700.TEC.INSP.HE 0
    51201 19700.DES.2D.HE 0
    51201 19700.ENG.CON.DOM 0
    51201 19700.ENG.S.DOM 0
    51201 19700.ENG.P.DOM 0
    51201 19700.ENG.JR.DOM 0
    51201 19700.PROJ.S.DOM 0
    51201 19700.PROJ.P.DOM 0
    51201 19700.PROJ.JR.DOM 0
    51201 19700.TEC.PLAN.DOM 0
    51201 19700.TEC.INSP.DOM 0
    51201 19700.DES.2D.DOM 0

    Thank you. Im sorry if its not so clear. Any help is perfect.

  • I have a multiple reports that I import to excel from an external source that I only neeed a small amount of data from. I have set up a macro to create a new sheet and copy what I need across to the new sheet. However, some information I need is always in the same row but is never in the same columns. The rest of the row is blank cells but the 2 bits I need are in different columns for each report that I import.

    Can I create a loop that will:
    a) go through each cell in the row until it finds a non empty cell and copy it then pause this loop
    b) then loops through a column on a different sheet to find the first empty cell and paste
    c) resumes first loop to find the next empty cell and continue to do ‘a & b’ until it gets to the end of the range

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter