VBA Loop and Objects

Hi ZmeY

Difficult to see what you are trying to achieve here. Are you trying to scroll down a list of values and insert a row between two that are different? e.g.:
1
1
1
<----Insert here
2
2
2

Anyway - some pointers on the code:
You don't need to select something to look at it. You can just refer to its address, or location:

Please Login or Register  to view this content.

The Rows command requires numbers, or a string. Unfortunately, "row:row2" will not be evaluated correctly. You could build the string:
strRows = Format(row,"0") & ":" & format(row2,"0")

Kind of messy. In this case, since row2 is always 2 rows beneath row, you could use:

Please Login or Register  to view this content.

To list through a range of cells, try this:

Please Login or Register  to view this content.

However, for inserting rows this all gets a bit tricky, as when you insert a row it affects the following rows. You end up in a perpetual loop.

So, start at the bottom and work your way up. Use row numbers, not cells/ranges, as these don't move about. If you insert a row then move above it, you know where you are and the rows you're now working on won'y have been affected by the previous insertion.

Please Login or Register  to view this content.

Hope some of this helps.

Cheers, Rob.

Hey I am definately will use Row formulas, thank you. and Example you showed me exacly what I want... but I want to compare like column 15 lets say...so that's why I was thinking to build a loop so I can do whatever I need in there. Could you I guess explain me when you select range what is return value? maybe I'll be able to implement it better, since inside those ranges I will have strings.

Strings to compare. and My break out of recursive loop will be count, basically if I see 4 empty cells in a row.

Thank you, sorry for 2 posts

Hi ZmeY

An example spreadsheet may help - I'm struggling to understand what you mean. To upload a sheet, press the Go Advanced button below, then Manage Attachments.

Anyway...

In the Cells command above you can look at any column you want. Column 15 would be:

Please Login or Register  to view this content.

Cells refers to the Cell within in the selected range. So, in this case, it is Row 1 of rows(i) and column 15, because we are referring to only row number i.

It can also be used with a worksheet: Sheets("Sheet1").Cells(16,12) would be row 16, column 12 (column L).

Count: The UsedRange command means that the code won't loop forever. Also note, my code counts up the page, not down it. If you introduce this and there are some empty rows at the bottom of the page, the code may stop before you have even started looking at the real data.

If you do want to use the count, then your implementation above is fine (Do Until... count=count+1... Loop)

Select: The select command is used to select a cell/range, then you use Selection to do things with that cell/range. It is useful when you want to move a user to a certain cell, or when a macro is to do something with the cell the user selected.

When writing the kind of macro for checking an entire sheet, Select isn't necessary. In fact it can be annoying, as it moves the user away from where they are currently working. It also results in the screen flickering around (unless you set off ScreenUpdate=False). It is easier and quicker to refer to cells using rows, columns, etc and not select them. The difference in the code is:

Please Login or Register  to view this content.

I have not moved the user from the sheet they were on, or wherever they are on that sheet, nor left them with a bunch of selected ranges.

Hope this helps.

Best regards, Rob.

Hey, thank you I figured out how to do it with loops, I'll keep that in mind though, its very helpful information. I still would need to figure out which row to insert at, which something I needed an if statement for, so I used a loop for it, and had it checking to adjacent rows. Then I think I did use your code , just had to manipulate few things to get row number. Thank you Rob

No probs. Glad it was of use.