Multiple loop inside one another

[sastoka]

Hi all

Window 7 / excel 2010

Here comes my problem, I get workbooks full of data but not organised except for one thing the phone number is in the cell below the cell with the valuue "Phone"

Currently I can retrieve each number in a sheet and create the list on the sheet 6.

here is my code

Sub RETRIEVE_AND_LIST()

Dim Lrow As Long
Dim Lcol As Long
Dim Lastrow As Long
Dim i As Range

Lrow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Lcol = Sheets("sheet1").Cells(1, Columns.Count).End(xlToLeft).Column


For Each i In Range(Cells(1, 1), Cells(Lrow, Lcol))
    If i.Value = "phone" Then
        Lastrow = Sheets("Sheet6").Cells(Rows.Count, "A").End(xlUp).Row
        i.Offset(1, 0).Copy Destination:=Sheets("Sheet6").Range("A" & Lastrow + 1)
    End If
Next i


End Sub
End Sub

then i want it to loop through each worksheet and it doesn't work .

I've tried with this

Sub RETRIEVE_AND_LIST()

Dim Lrow As Long
Dim Lcol As Long
Dim Lastrow As Long
Dim i As Range
Dim w As Worksheet

Lrow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Lcol = Sheets("sheet1").Cells(1, Columns.Count).End(xlToLeft).Column

For Each w In Worksheets
    For Each i In Range(Cells(1, 1), Cells(Lrow, Lcol))
        If i.Value = "phone" Then
            Lastrow = Sheets("Sheet6").Cells(Rows.Count, "A").End(xlUp).Row
            i.Offset(1, 0).Copy Destination:=Sheets("Sheet6").Range("A" & Lastrow + 1)
        End If
    Next i
Next w

End Sub

And later i'd like it to loop through all the open workbook.

Note that all workbook have the same format name (1.Guestphonelist to 11.Guestphonelist). All contain 6 worksheets and I want to add everything in the worsheets("Sheet6")

I can't get the loop to work.

If some could help itd be appreciate.

Thx

 

[iliace]

I think the first small change you may want to make is this (in bold/red). Also, moved the Lrow, Lcol calculations inside the loop to work for each sheet. You'll have to make similar adjustments once you go to process all sheets in all workbooks. Each member needs to be qualified.

Sub RETRIEVE_AND_LIST()


Dim Lrow As Long
Dim Lcol As Long
Dim Lastrow As Long
Dim i As Range
Dim w As Worksheet






For Each w In Worksheets

  ' ignore Sheet6
  If w.Name <> "Sheet6" Then
    Lrow = w.Cells(w.Rows.Count, "A").End(xlUp).Row
    Lcol = w.Cells(1, w.Columns.Count).End(xlToLeft).Column


    For Each i In w.Range(Cells(1, 1), Cells(Lrow, Lcol))
        If i.Value = "phone" Then
            Lastrow = Sheets("Sheet6").Cells(Rows.Count, "A").End(xlUp).Row
            i.Offset(1, 0).Copy Destination:=Sheets("Sheet6").Range("A" & Lastrow + 1)
        End If
    Next i
  End If
Next w


End Sub

Also, in the future, I suggest when you have a Range object, don't use "i" as the name of the variable. Use "r", "rng", "obj", etc. Names like i,j,k,n,m,x,y - those are used in traditional programming to indicate integer/long variables, usually loops or counters. A Range is a class variable, it needs some more distinction in your code - if for no other reason, to improve readability.

 

[sastoka]

Hi,

I get a Method 'Range' of Object '_Worksheet' failed.

on this line

Sub RETRIEVE_AND_LIST()


Dim Lrow As Long
Dim Lcol As Long
Dim Lastrow As Long
Dim r As Range
Dim w As Worksheet






For Each w In Worksheets

  ' ignore Sheet6
  If w.Name <> "Sheet6" Then
    Lrow = w.Cells(w.Rows.Count, "A").End(xlUp).Row
    Lcol = w.Cells(1, w.Columns.Count).End(xlToLeft).Column


[COLOR=#ff0000]    For Each r In w.Range(Cells(1, 1), Cells(Lrow, Lcol))[/COLOR]
        If r.Value = "phone" Then
            Lastrow = Sheets("Sheet6").Cells(Rows.Count, "A").End(xlUp).Row
            r.Offset(1, 0).Copy Destination:=Sheets("Sheet6").Range("A" & Lastrow + 1)
        End If
    Next r
  End If
Next w


End Sub

apparently it can't define a range but if I step into the code the variable Lrow and Lcol return me a value. If I get rid of the w

[COLOR=#ff0000]   [/COLOR] For Each r In .Range(Cells(1, 1), Cells(Lrow, Lcol))[COLOR=#ff0000][/COLOR]

it loops 5 time through the tange in sheet1 wich sounds correct to me.

Any idea why I can't define my range.


Regards

 

[iliace]

Sorry, you may have to adjust those .Cells references as well.

Try this instead:

For Each r In w.Range(w.Cells(1, 1), w.Cells(Lrow, Lcol))

 

[sastoka]

thx iliace,

worked perfectly. also I do not understand why do i need to locate the cell in the worksheet if i've already located the range in the workbook.

but it's working.

 

[iliace]

When you use the Range method using two ranges as parameters, it expects to receive range objects.

Of course, the Range objects have to be on the same sheet as the Range method that's being called.

Using Cells collection, if you do not specify what Worksheet object it belongs to, defaults to the ActiveSheet.

Therefore, you are trying to create a Range object between two ranges on another sheet.

Essentially, it's like saying: Sheet2!Sheet1!A1:A10 - doesn't make sense.

In general, it is always a good practice to explicitly state which sheet you're working with, unless you literally want whatever the ActiveSheet happens to be. I generally do this:

Dim wsh As Excel.Worksheet
Set wsh = Application.ActiveSheet

Note, I also explicitly state which Application object the ActiveSheet should be from, since there can be multiple ones being used by the project. Also note, I explicitly state that the Worksheet class is part of the Excel class library. This is in case there are multiple libraries that have a Worksheet class. With this class, it's unlikely, but consider the following automation:

Dim objWord As Word.Application
Dim rng As Range

I'm using early binding, and the Word library is referenced in my project. Both Word and Excel have a Range class, and the two are quite different. So when I declare it as just "Range", VBA doesn't know which one I meant; I have to specify explicitly:

Dim wdRng As Word.Range
Dim xlRng As Excel.Range

And in the general case, I've made it a pet peeve obsessive-compulsive habit of mine to be as specific as I can. At one point, it got so bad that instead of writing this:

str1 = Left$(str2, 5)

I would write this:

str1 = VBA.Left$(str2, 5)

Hope this helps (or at least informative).