Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!


Free Excel Forum

Loops And Counters

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Half the battle with this one is in describing the problem . . .

If someone could help with getting me started; I’d like to know how to use a loop counter (For-Next) in conjunction with a second counter that adjusts the integer on each loop. I'm hoping that describes the problem but, if not, maybe this explains it better:

I have many columns of data – only three rows high - containing all kinds of combinations of numerics.

Initially, I’m looping though to find the first occurrence of a particular combination:

Row1 = 2.2
Row2 = 3.4
Row3 = 4.6
(When I find that combination I copy, transpose and write it to another sheet. I can do that part.)

Next loop ‘round I want to ignore the above – as I already have the first occurrence of that sequence – and instead find the first occurrence of:
Row1 = 2.1
Row2 = 3.4
Row3 = 4.6

And after that:
Row1 = 2.0
Row2 = 3.4
Row3 = 4.6

Finally – here’s the painful part – I want another loop to do similar things with the second number. For example:
Row1 = 2.2
Row2 = 4.0
Row3 = 4.6
But I’ll worry about that part later . . .

Any guidance - especially on the first stage with a loping counter and a variable counter - very gratefully received!


View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
Vlookup Macro to Return All Matching Results from a Sheet in Excel
- This Excel Macro works like a better Vlookup function because it returns ALL of the matching results. Run the
Delete Duplicate Rows
- This macro will delete rows that appear twice in a list or worksheet. If two cells are identical, this macro will delete
Vlookup Macro to Return All Matching Results and Stack them with Previous Results
- This is very similar to the other Vlookup type Macro in that it returns all of the results that match a particular se
Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a

Similar Topics

Hey again guys,

I need to find a way for the IF function to perform some tricky calculations (at least for me). I have three rows. If the value in one row is less than half of the value of the other two rows, then it is true.

Here is an example:
row1 row2 row3
38 53 93

We see that row1 is less than half of row3 but not of row2. this other case:

row1 row2 row3
53 21 81

Row2 is less than half of either of the other two. How would I create a formula to do that, please? Thanks!

I have a problem that is likely very simple, but which has stumped me. I have a list of values that look like the following:

Row1 Col1 Value
Row1 Col2 Value
Row1 Col3 Value
Row1 Col4 Value
Row2 Col1 Value
Row2 Col2 Value
Row2 Col3 Value
Row2 Col4 Value
Row3 Col1 Value
Row3 Col2 Value

What I really want is:
Row1 Value Value Value Value
Row2 Value Value Value Value
Row3 Value Value Value Value
Row4 Value Value Value Value

Is there any easy way to do this easily? Sometimes I have up to 500 rows and columns, so manual cut-and-paste is out of the question. Thanks in advance for any assistance.


I am trying to see if I can do the following in Excel......

Column A
Row1 5/09/2008 8:28
Row2 5/09/2008 8:28
Row3 5/09/2008 8:28
Row4 5/09/2008 8:28

Column B
Row1 is empty
Row2 is empty
Row3 RWLU214846
Row4 RWTU9711617

Column C
Row1 RWTU9627105
Row2 CRXU0778382
Row3 is empty
Row4 is empty

Column D
Row1 5/09/2008 8:48
Row2 5/09/2008 8:48
Row3 5/09/2008 8:48
Row4 5/09/2008 8:48

Column E
Row1 VSW640
Row2 VSW640
Row3 VSW640
Row4 VSW640

Column F
Row1 5814851
Row2 5814851
Row3 5814851
Row4 5814851

I am after a formula that will give the following results in Column H, I

The above data may be 200 or more rows, with different data, but most times there will be 2, 3 or 4 rows, with data in Column F being identical. This is the reference point.

Take Column D from Column A result Column H, ie in this case 00:20 minutes

As there are 4 rows that have an identical number in Column F, count the cells in Columns B & C that fall in the same 4 rows that have data in, result in Column I. ie in this case 4

Any Help appreciated



I have the following cell formula:


=IF(AND(D1=D2, C1=C2, B1B2, E1E2), TRUE, FALSE)

Which checks two rows to meet the above conditions.

Now I want to write a code where every row in the worksheet is compared to all rows in the worksheet for the above conditions.

So row1 with row2, row3, row4...
then row2 with row1, row3, row4...
then row3 with row1, row2, row4...

Thanks guys for your help!

Hi All,
I am receiving a quite large number of tab separated text files with
information each week (50+), that I need to import into an Excel sheet and
then work with=produce reports
It is quite time consuming to run the File import utility for each file, and
I would therefore like to create a macro assigned to a button that should do
the following.

For each text file in a folder, sorted by file name or creation date, do the
1. Import the tab separated data, and append it to the already existing data
in the active sheet. The information should be imported like the example below
2. Move the file to another folder, an "archive"

If I have this I can easily create a rule in Outlook that stores my text
files in a certain folder, open excel, push the button, and be ready to work.
Would be great!

The file that should be imported looks like this:
Colummn Header 1 Column Header 2 Column Header 3
ROW1-1 ROW1-2 ROW1-3
ROW2-1 ROW2-2 ROW2-3
ROW3-1 ROW3-2 ROW3-3

Ideally, I would like the excel spreadsheet to look like this:
Header2 ROW1-1 ROW1-2 ROW1-3
Header2 ROW2-1 ROW2-2 ROW2-3
Header2 ROW3-1 ROW3-2 ROW3-3

I have tried to write macros for this but cannot make it work. If all cannot
be done (I realize this might be quite complex) I would appreciate any help
with getting as close as possible to the ultimate solution!

Many thanks,

I have an array of data in a table and I want to convert it into a list [see attached pic for clarity]...

What I have:
Col1 Col2 Col3
Row1 xx ss tt
Row2 yy rr uu
Row3 zz qq jj

What I want:
ListCol1 ListCol2 ListCol3
Row1 Col1 xx
Row1 Col2 ss
Row1 Col3 tt
Row2 Col1 yy
Row2 Col2 rr
Row2 Col3 uu
Row3 Col1 zz
Row3 Col2 qq etc...

I know that once I have the values set up in ListCol1 and ListCol2, I can use an =INDEX(..,MATCH(ListCol1..),MATCH(ListCol2),...) sort of setup to get the values for ListCol3. My problem is how do I fill in the first two columns?

Thanks in advance,

Hi Guys,

I have been trying to sort a two-dimensional array in excel using VBA.
the values of the array are all strings not numbers, which has been causing my head to explode.

I have an excel sheet that contains data 4 rows by 4 columns.
I then want to put that data in to an array so I can use it.

This is the first time I have been playing with 2D Arrays in VBA. From what I gathered this is how it should look:


    Dim MyArray(1 To 4, 1 To 4)
    MyArray(1, 1) = "A col1 row1"
    MyArray(1, 2) = "C col1  row2"
    MyArray(1, 3) = "D col1  row3"
    MyArray(1, 4) = "B col1  row4"
    MyArray(2, 1) = "D col2  row1 - sort this col"
    MyArray(2, 2) = "B col2   row2 - sort this col"
    MyArray(2, 3) = "A col2   row3 - sort this col"
    MyArray(2, 4) = "C col2   row4 - sort this col"
    MyArray(3, 1) = "A col3  row1"
    MyArray(3, 2) = "D col3 row2"
    MyArray(3, 3) = "B col3 row3"
    MyArray(3, 4) = "C col3 row4"
    MyArray(4, 1) = "D col4 row1"
    MyArray(4, 2) = "C col4 row2"
    MyArray(4, 3) = "B col4 row3"
    MyArray(4, 4) = "A col4 row4"

so going by this principal i tried to sort the array by the 2nd column. I tried various examples from other sites but they all say that "subscript out of range"

can anyone please let me know if I am going about this completely the wrong way. Any help greatly appreciated. Thanks


    Dim MyArray(1 To 4, 1 To 4)
    Dim i As Integer, j As Integer

    MyArray(1, 1) = "A col1 row1"
     MyArray(1, 2) = "C col1  row2"
     MyArray(1, 3) = "D col1  row3"
     MyArray(1, 4) = "B col1  row4"
     MyArray(2, 1) = "D col2  row1 - sort this col"
     MyArray(2, 2) = "B col2   row2 - sort this col"
     MyArray(2, 3) = "A col2   row3 - sort this col"
     MyArray(2, 4) = "C col2   row4 - sort this col"
     MyArray(3, 1) = "A col3  row1"
     MyArray(3, 2) = "D col3 row2"
     MyArray(3, 3) = "B col3 row3"
     MyArray(3, 4) = "C col3 row4"
     MyArray(4, 1) = "D col4 row1"
     MyArray(4, 2) = "C col4 row2"
     MyArray(4, 3) = "B col4 row3"
     MyArray(4, 4) = "A col4 row4"
 SortColumm1 = 1
 SortColumn2 = 3
 For i = LBound(MyArray, 1) To UBound(MyArray, 1) - 1
      For j = LBound(MyArray, 1) To UBound(MyArray, 1) - 1
          Condition1 = MyArray(j, SortColumn1) > MyArray(j + 1, SortColumn1)
          Condition2 = MyArray(j, SortColumn1) = MyArray(j + 1, SortColumn1) And _
                     MyArray(j, SortColumn2) > MyArray(j + 1, SortColumn2)

          If Condition1 Or Condition2 Then
               For y = LBound(MyArray, 2) To UBound(MyArray, 2)
                   t = MyArray(j, y)
                   MyArray(j, y) = MyArray(j + 1, y)
                   MyArray(j + 1, y) = t
                   Next y
               End If

Hi Experts,

I am a novice in using VBA or Macros in excel. I need a help in copying all the available rows from Individual Sheet say A, B & C that needs to be copied to Sheet D in the given below fashion in a Worksheet:

Copy Row1 from Sheet A
Copy Row1 from Sheet B
Copy Row1 from Sheet C

Output Results in Sheet D should be:
Row1 = Paste Row1 from Sheet A
Row2 = Paste Row1 from Sheet B
Row3 = Paste Row1 from Sheet C
Row4 = Paste Row2 from Sheet A
Row5 = Paste Row2 from Sheet B
Row6 = Paste Row2 from Sheet C

I have around 4224 rows in Sheets A, B & C that needs to be copied to Sheet D. Hope i have explained what i am looking at above clearly. If you need any clarifications then, please let me know.

Your help would be greatly appreciated...

Thanking a lot in advance...

Thanks & Regards,

Hi All,

In Excel 2007 sheet i have a column whose type is text. It contains date time stamp in chronological order but the difference of time between two adjacent rows is not same. What i mean is this:

Row1: 2011-01-21 09:17:33.0
Row2: 2011-01-21 09:18:33.0
Row3: 2011-01-21 09:27:33.0
Row4: 2011-01-21 09:29:33.0
Row5: 2011-01-21 09:33:33.0
Row6: 2011-01-21 09:38:33.0
Row7: 2011-01-21 09:47:33.0
Row8: 2011-01-21 09:51:33.0
Row9: 2011-01-21 09:60:33.0
Row10: 2011-01-21 09:66:33.0

The time difference between Row1 value and Row2 value is approx. 1min.
The time difference between Row2 value and Row3 value is approx. 9min.
The time difference between Row3 value and Row4 value is approx. 2min.
The time difference between Row4 value and Row5 value is approx. 4min.
so on...

Now i want a new coulmn that has following values in its rows:

Row1: 0
Row2: 1
Row3: 10
Row4: 12
Row5: 16
so on...

As you can see the values add-up in every next row.

Can anyone guide me how it can be done? Any help would be great.


I have:

Col A----Col B----Col C
row1 cow cat
row2 dog
row3 sheep

I need:

For all rows, if Col B has data but Col C does not, move data from Col B into Col A and down one row

Col A----Col B----Col C
row1 cow cat
row3 dog
row4 sheep


This is my first ever post as I'm new to VBA, so forgive any sloppy code or inadvertant breach of etiquette.

My problem is I’m not able to influence the ending row (where the loop stops) of a ‘For’ loop to reflect rows inserted during its execution.

I am writing a spreadsheet macro where there will be a different number of rows each time it is run. My macro correctly determines the final row and then runs a ‘For’ loop to process every row.
When I encounter a change of, in this case a contract number I insert a line and I then increment my counter and the variable holding the row count by one.

The problem is that the execution still stops when the current row is equal to the starting value of the variable final row – stopping short of the last row. How do / can I influence where the loop stops once I’m in the loop?

Dim FinalRow            As Integer
Dim i                       As Long
FinalRow = Cells(65536, 1).End(xlUp).Row
For i = 2 To FinalRow
 i = i + 1
 Cells(i, 1).Select
 ActiveCell.EntireRow.Insert shift:=xlDown
 FinalRow = FinalRow + 1
Next i

dear everyone,
I have 3 columns say
colA colB Col C
row1 1 A pen
row2 2 B sit
row3 1 A read

now i want my output to be like

colA colB Col C
row1 1 A pen
row2 read
row3 2 B sit

that is i want to group all the As in the column B and allow it to display
only once for howmany ever rows we have in col C.
The main thing is that these rows get filled from another sheet. therefore
they are dynamic, i may have any number of rows in this sheet.
I want to do this kind of grouping inside a macro. kindly help

Thread Locked as Title infringes forum rules

I have an excel chart ( XY scatter, compares pairs of values.)

How can I make it read data the following way:
for the first point
legend comes from (Column A:row1)
x axis value comes from (columnA:row2)
y axis value comes from (columnA:row3)

for the second point
legend comes from (Column B:row1)
x axis value comes from (columnB:row2)
y axis value comes from (columnB:row3)

for the third point
legend comes from (Column C:row1)
x axis value comes from (columnC:row2)
y axis value comes from (columnC:row3)

and so on...

Thanks for any help anybody can give...


I have a csv file with roughly 4k rows. I am trying to avoid doing this process manually. What I need is to duplicate every row and change a few values systematically. So real world example is something like this:




Then I need to

delete the values from columns B,C,D,F from just the copied rows.
delete the value from column H and change it to "SCAN" on just the copied rows
append "-scan" to the end of column M on just the copied rows
column S value needs do be deleted and changed to 6.99 on just the copied rows
columns U and V need to be deleted and both changed to "FALSE"
column W value needs to be deleted.

Is something this complicated even possible? And if so where, oh where do I start?

Any and all tips/advice is greatly appreciated. If there is a service that can build this as a (macro?, function?) for a fee that would even work.

Thanks! and Best!

Hi Folks,
I use Access to extract relevant data via queries and create tables. Excel then retrieves the data from the tables made in Access (via the ‘Data – Import External Data – Import Data’ method). I use Excel’s ‘External Data’ Toolbar to ‘Refresh All’ in the Excel workbook. I have formula on several worksheets which I would like to protect from accidental changes. THE PROBLEM IS:- If I apply protection (‘Tools – Protection-Protect Worksheet’), the ‘Refresh All’ control on the ‘External Data’ Toolbar does not work.
If anyone knows how to resolve this problem I would be very pleased to hear from you and exceedingly grateful. I am not an expert in Excel and so will probably need step by step explanations of how to carry out whatever suggestions you kindly offer. David M Wright

I have an excel chart ( XY scatter, compares pairs of values.)

How can I make it read data the following way:
for the first point
legend comes from (Column A:row1)
x axis value comes from (columnA:row2)
y axis value comes from (columnA:row3)

for the second point
legend comes from (Column B:row1)
x axis value comes from (columnB:row2)
y axis value comes from (columnB:row3)

for the third point
legend comes from (Column C:row1)
x axis value comes from (columnC:row2)
y axis value comes from (columnC:row3)

and so on...

Thanks for any help anybody can give...


Question 1:
I would like to alert the user if a line of data does not adhere to the following validation rules listed below. The user will then need to manually correct the data and the template will not perform any auto-correction of the data for the user.
a) Header – Check that ‘PODAgentDetails’ exists.
b) SO – Check that the length is 10 digits.
c) SR – Check that the length is 11 characters long (inclusive of ‘SR’ characters)
d) CourierDay & PODDay – Check that the date is in the correct mm/dd/yyyy format. It will also check if that the ‘mm’ field is between 1 to 12.
e) CourierTime & PODTime – Check that the time is in the correct hh:mi:ss format. Check that ‘hh’ is between 0 to 23, ‘mi’ is between 0 to 59 and ‘ss’ is between 0 to 59

Question 2:
The input of the data to this excel sheet, might be key in or paste. How to protect the cell validation rules if we paste one row or column data on the it.

Question 3:
Any suggestion how to let the user manually see the problem of the key in or paste data format is wrong? ( maybe auto checking - circle/colour the cell)

Please download the attachment and have a look.

Thanks for all suggestion for helping.


I would like to select cell based on the result of other cells in a different row. I have 3 rows and 12 coulombs. Row1 is the number of hours, Row2 is the energy used, Row3 is the result of Row1 divided by Row2 which is a ratio. The 12 coulombs are number 1 to 12 and are the months in the year. I need to be able to find the lowest ratio result in Row3 and then based on that result select the corresponding cell in the same coulomb but in Row1.
Any help would be much appreciated
Thanks Bernard


I'm not sure how to describe what I am trying to do in excel. Please no VBA.

I have 3 sheets. #1 and #2 have items listed in a column. #3 is a summary, like below. I want sheet #3 to display items that are in one list, but not in the other...

Here is my example:

sheet #1 - unique values
Row1: apples
Row2: oranges
Row3: pears
Row4: strawberries
Row5: bananas
Row6: mango

sheet #2 - repeat values are possible
Row1: oranges
Row2: bananas
Row3: grapes
Row4: grapefruit
Row5: kiwi
Row6: strawberies
Row7: pears
Row8: oranges
Row9: oranges
Row10: kiwi

sheet #3
ColumnA: items in sheet 1 (A1:A6), but not in sheet 2 (A1:A10):
Row1: apples
Row2: mango

ColumnB: items in sheet 2 (A1:A10), but not in sheet 1 (A1:A6):
Row1: grapes
Row2: grapefruit
Row3: kiwi
* Do not repeat "kiwi" even though it is found 2 times in sheet #2 and none on sheet #1

thank you for your patience.

I was wondering if anybody could help me. Here is my problem:

Row1 ? Row2 10 Row3 20 Row4 Sum(Row1:Row3) I want to make Row1 equal to a number that is always going to make my sum equal to 1.5, how can I do that? That would not only create a circular reference but I am not sure about the formula.

Thanks for the help,


I do have a database I’m trying to build and have a problem – In our plant we do small maintenance work on the machines (tasks) – in order to track these tasks I created a spreadsheet that lists the tasks and when the operator completes the task – they hit a macro button that simply dates the task and files it away in a data tab – reseting the field for the next entry – then I take a pivot table for the data tab and summarize the data – what is up to date and what is behind –

My problem is how do I create a sheet that the operator can click completed on the task and submit the data to the other workbook – resetting the task for next time. WITHOUT USING MACROS - everyone is worried about macros and not being able to see how they work in case i move departments - I want to make the data store step more visible (simple) - I saw userforms but don’t think that is my best option. Any help is greatly appreciated.


I will appreciate your help on my project. As shown in the attached file, I have data from Columns A to G. I want to do the following:

1. Look for duplicates in all column A
2. For each duplicate found in column A, check if all values in columns B, C and D are also duplicates (The condition is all 4 columns must have the same values)
3. If the condition in (2) is satisfied, compare column E for all the rows; select the row with the minimum value in column E, and delete the other rows.

For example:


Row1:Test01 100 Class 1 1 80.67 6.47 145
Row2:Test01 50 Class 1 3 80.25 6.71 598
Row3:Test01 100 Class 1 1 77.72 6.62 110

Since Rows 1 and 3 have the same values in columns A, B, C and D; and Row3 value in column E is less than Row1 value (77.72<80.67); Delete Row1



Row2:Test01 50 Class 1 3 80.25 6.71 598
Row3:Test01 100 Class 1 1 77.72 6.62 110

Thank you for your help!


I tried to find a macro on this forum which can transpose a set of rows to columns, but could not find one that solves my needs. Can anyone help me out? Much thanks in advance

Problem description (sample Excel 2010 File attached):

> Excisting Data

Row1: WireWay
Row2: Manufacturer of wire-management control systems
>Row4: Empty Row
Row5: Marrow Lin Development Co., Ltd.
Row6: Manufacturer of copper terminals
>Row8: Empty Row till end

> Need to become:

Row1>Column1: WireWay
Row1>Column2: Manufacturer of wire-management control systems including modular
Row2>Column1: Marrow Lin Development Co., Ltd.
Row2>Column2:Manufacturer of copper terminals


Row2 A2:12 B2:25 C2:36 D2:48 E2:56 G2:56
Row3 A3:1 B3:0 C3:1 D3:2 E3:4

Row2 shows 5 different numbers

Row3 relates to the Row2 number, Row 3 is the number of times Row2 number has hit.

I'm looking for a formula which picks the "highest" hit number (Row3) and places the appropriate Row2 number in the G2 cell.

This would be an automatic update since hits per number increase over time.

Thanks for the help...

Hi guys,

I have a problem with VBA I need to overcome.

OK, so, I have three Worksheets:

Data_Sheet – this contains a table of data with three columns; Date, Sales, Cost
Pivot_Sheet – this contains a pivot table of the data within the Data worksheet
Controls_Sheet – This contains a parameter which I would like to use to control the pivot table

What I would like to do is to change the date value in Controls_Sheet!B2, then run a macro, which would then filter all date values except the value within Controls_Sheet!B2.

So for example, if these are my dates within the ‘Dates’ column of my Pivot Table:


Then I set Controls_Sheet!B2 equals ‘04/09/2010’ then run the macro, only the line for ‘04/09/2010’ will be showing in the pivot table.

I have tried many codes all which seem not to work, so I haven’t posted them here. I have posted the example work sheet.

I’m sure this is simple buy I’m struggling with it. Thanks in advance.