Macro - question about command line

[Lis7]

I have a macro that someone built from an example worksheet (below), which I need to customize for my actual worksheet. Can someone please tell me what the following command line does and very generally how "ii" gets used in the rest of the macro, so I know what I need to change: For ii = 2 To 4

Worksheet: Excel category example.xlsx

(The macro, copied below, reads the values in columns B, C and D; assigns a category and places it in column E. The actual worksheet has many more rows and columns.)

Thank you!
Lis

Macro:

Please Login or Register  to view this content.

[AB33]

Please use code tags with your code

Please Login or Register  to view this content.

You have nested loop. For every row, the code loops from column 2 to column 4., or 3 times
The columns are nested with in the row

[TMS]

ii is being used as an index, a counter ... In this case, a column counter. Similarly, i is being used as a row counter. So, you have a loop within a loop. The outer loop is processing rows and the inner loop is processing columns within the rows. The intersection of the row and the column is a cell.

The code is testing Cells(i, ii) for various conditions.


Regards, TMS

[duncandhu]

Hi Lis7,

Here's a site with an explanation of For..Next loops:
http://www.excelfunctions.net/VBA-Loops.html

Basically i is the row marker, and ii is the column marker. It's going through all the cells in between row 2 and LastRow, and then through columns 2 to 4, and doing whatever in between.

[Lis7]

Sorry, I looked in the FAQ before I posted the code and I didn't see code tags mentioned, maybe I missed it [UPDATE: fixed now]

Thank you all for your answers. This may be a stupid question, but in the real spreadsheet, the columns that "ii" will reference are way out to the right - so do I need to use the numbers that correspond to the column positions, e.g. 30 To 38?

Also, is there a way to loop through specific columns, instead of a range? There are columns in between the "Code 1, Code 2, Code n... columns that need to be ignored. (Otherwise, I'll reorder the columns so they're in a group, but would prefer not to if I can.)

Thank you.

[AB33]

The code works on the attached as the number of columns are 5, but you change the columns to 30 to 38 which means you are looking from column AE to AM.

[Lis7]

Okay, I created the macro in the spreadsheet, tried to run it, and it threw "Run-time error '438' Object doesn't support this property or method. When I ran debug, it is pointing toward this line:

Please Login or Register  to view this content.

.

(Macro code):

Please Login or Register  to view this content.

Here is the original spreadsheet (the "no macro" version): Excel category example.xlsx
I'm using Excel 2007. Thanks again, everyone has been so helpful

[TMS]

I think you need Activesheet.UsedRange.Rows.Count


Regards, TMS

[Lis7]

Trevor, that did the trick, thanks! ))) Now on to the real spreadsheet.... will keep this thread open until everything is working.

[TMS]

You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[TMS]

will keep this thread open until everything is working.

The original question was answered in some detail, and a couple of follow-up questions were also answered.

You should mark this thread solved regardless of what new questions rear their heads when you start to apply what you have learnt to the live workbook.

If you have new questions, you should start new threads.


Regards, TMS