Loop until won't stop!?

**meacho** · 07-27-2007, 03:49 AM

the value of x1up when it finishes is -4162 but i only have 2 row in this test example? should it not be 65536 less 2?

i have tried clearing the cells below my data but it didnt help?

thanks

**antoka05** · 07-27-2007, 04:13 AM

The instruction

Cells(Rows.Count, 2).End(xlUp).Row 'the 2 means column 2

do the same thing than go to max row of column 2 (for example B65536) then press 'End' and 'Up' to go to the last row of column 2.

To fix your problem try to do so:

lastRow = Sheets("export").Cells(Rows.Count, 2).End(xlUp).Row 'the 2 means column 2

For i = 1 To lastRow
   'your code
next i

Regards,
Antonio

**meacho** · 07-27-2007, 04:44 AM

thanks for the help.

i sorted it buy just using

n=1

my coded

n=n+1
end sub

thanks for all the help its mcuh appreciated

any thoughts on the deleting sheets questions? (see post)

**mudraker** · 07-27-2007, 03:37 PM

Why do you have a For loop inside a Do Loop?

Instead of seleting sheets & ranges all the time you could use something like this code

Macro1()
   Dim lLR As Long
   Dim lRow As Long
   Dim wsE As Worksheet
   
   Dim sSheetName As String
   
   Application.ScreenUpdating = False
   
   Set wsE = Sheets("export")
   lLR = wsE.Cells(Rows.Count, "a").End(xlUp).Row
   For lRow = 2 To lLR Step 1
      sSheetName = wsE.Cells(lRow, "ar").Value
      Sheets("Template").Copy After:=Sheets(1)
      Sheets("Template (2)").Name = sSheetName
      Sheets(sSheetName).Select
      
      wsE.Cells(lRow, "a").Copy
      Range("E2:AA2").Select
      ActiveSheet.Paste

      wsE.Range("b" & lRow & ":c" & lRow).Copy
      Range("h10:h11").Select
      ActiveSheet.Paste

      wsE.Cells(lRow, "d").Copy
      Range("E6").Select
      ActiveSheet.Paste
    
      wsE.Cells(lRow, "at").Copy
      Range("e38").Select
      ActiveSheet.Paste
   Next lRow
   Set wsE = Nothing
End Sub

Loop until won't stop!?

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions