+ Reply to Thread
Results 1 to 4 of 4

Loop not working properly - code included - help?

Hybrid View

  1. #1
    Registered User
    Join Date
    10-23-2007
    Posts
    80

    Loop not working properly - code included - help?

    Hello,
    I have the following but of VBA code as a loop. For each loop (from rows 2 to 50) I want it to check whether the value in column U is 0 and if it is to just move straight to the next row/loop... if the data is not 0 I want it to run the rest of the code. However, it runs through 2 loops (the first has 0 in column U, the second doesn't) and then just stops... so I think I have something the wrong way round?

    
        Sheets("Info").Select
        
        For x = 2 To 50
        
        If Range("U" & x).Value = 0 Then
    
        Else
        
        Sheets("Info").Select
        
        C = Range("B" & x).Value
        D = Range("M" & x).Value
        
        Sheets("DATA").Select
        
        Selection.AutoFilter Field:=18, Criteria1:=C
        Range("A1").Select
        Range(Selection, Selection.End(xlDown)).Select
        Range(Selection, Selection.End(xlToRight)).Select
        Selection.SpecialCells(xlCellTypeVisible).Select
        Selection.Copy
        
        Workbooks.Add
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Columns("R:R").Select
        Selection.Delete Shift:=xlToLeft
        Application.DisplayAlerts = False
        ActiveWorkbook.SaveAs Filename:= _
            D, FileFormat:= _
            xlOpenXMLWorkbook, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
            , CreateBackup:=False
        Application.DisplayAlerts = True
        
        ActiveWindow.Close
        
        Windows("Bulk Upload File.xlsm").Activate
        End If
        Next x
            
        
        End Sub
    Last edited by lealea1982; 02-24-2015 at 12:34 PM.

  2. #2
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Loop not working properly - code included - help?

    If Range("U" & x).Value = 0 Then
    The way this is written, it is a variable range reference depending on the active workbook and active sheet. Since you are changing the active workbook I would guess it is failing there. Try specifing the workbook/worksheet for the range reference (i.e. Workbook.Worksheet.Range)

  3. #3
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Loop not working properly - code included - help?

    Does this work any better?
    Dim wbNew As Workbook
    Dim shInfo As Worksheet
    Dim shData As Worksheet
    Dim x As Long
    Dim C As Variant
    Dim D As String
    
        Set shInfo = Sheets("Info")
    
        Set shData = Sheets("Data")
        For x = 2 To 50
    
            If shInfo.Range("U" & x).Value <> 0 Then
    
                C = shInfo.Range("B" & x).Value
                D = shInfo.Range("M" & x).Value
    
                With shData("DATA")
                    .UsedRange.AutoFilter Field:=18, Criteria1:=C
                    .Range("A1", .Range("A1").End(xlDown).End(xlToRight)).SpecialCells(xlCellTypeVisible).Copy
                End With
                
                Set wbNew = Workbooks.Add
                With wbNew
                    With .ActiveSheet
                        .Paste
                        Application.CutCopyMode = False
                        .Columns("R:R").Delete Shift:=xlToLeft
                    End With
    
                    Application.DisplayAlerts = False
                    .SaveAs Filename:=D, FileFormat:=xlOpenXMLWorkbook
                    Application.DisplayAlerts = True
                    .Close
                End With
    
            End If
        Next x
    If posting code please use code tags, see here.

  4. #4
    Registered User
    Join Date
    10-23-2007
    Posts
    80

    Re: Loop not working properly - code included - help?

    Yes perfect, thank ou both!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] VBA if loop not working properly
    By asparak in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-10-2014, 01:39 PM
  2. [SOLVED] Code not working properly
    By TomRet in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 02-01-2014, 04:16 AM
  3. [SOLVED] How do i make my code loop properly?
    By Fjalar in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-22-2013, 05:23 AM
  4. Loop For not working properly
    By mabbutt in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-12-2008, 08:59 AM
  5. For Each Loop not working properly
    By oakman in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 03-26-2006, 06:44 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1