+ Reply to Thread
Results 1 to 3 of 3

Slight edit needed with code..

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    04-19-2013
    Location
    Yorkshire, England
    MS-Off Ver
    Excel 2010
    Posts
    297

    Slight edit needed with code..

    I have a code that currently works apart from where x is nothing....because for some reason it stills proceeds to attempt to paste x (which, I think, should only happen where If Not x Is Not Nothing)

    Essentially if x is nothing I would like to proceed to the second part of the code (the bottom half) but without attempting to paste x to a new sheet- which should only happen when If Not x Is Not Nothing because otherwise it is pasting nothing and takes me to the debugger...

    Apologies if I haven't explained that well...anyway here is the code:

    Sub Copy_AboveBellow_Error()
        Dim i As Long, x As Range
        With Sheets("Input")
            For i = 6 To .Range("g" & Rows.Count).End(xlUp).Row
                If .Cells(i, "g").Value <> .Cells(i, "h").Value Then
                    If x Is Nothing Then
                        Set x = .Cells(i, "e").Resize(, 3)
                    Else
                        Set x = Union(x, .Cells(i, "e").Resize(, 3))
                    End If
                End If
            Next
        End With
        If Not x Is Nothing Then x.Copy
        Sheets("Upload Sheet").Range("a" & Rows.Count).End(xlUp)(2).PasteSpecial _
                Paste:=xlPasteValues
        Application.CutCopyMode = False
    
    'second half 
    
    Dim bu As Boolean
    With Sheets("Pre-Check")
        For i = 6 To .Range("h" & Rows.Count).End(xlUp).Row
            If .Cells(i, "h") = "Error - Not Inputted" Then
                bu = True
                Sheets("Discrepancies").Cells(Rows.Count, "e").End(xlUp)(2).Resize(, 3).Value = .Cells(i, "e").Resize(, 3).Value
            End If
        Next i
    End With
    If bu Then
        MsgBox ("IMPORTANT (Discrepancies): there are some items from the pre-check that have a >0 count but that were not checked in your stock check...")
        Sheets("Discrepancies").Activate
    Else
        UserForm1.Show
        Sheets("Upload Sheet").Activate
         
    End If
    
    
    End Sub

  2. #2
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Slight edit needed with code..

    maybe so
    If Not x Is Nothing Then
        x.Copy
        Sheets("Upload Sheet").Range("a" & Rows.Count).End(xlUp)(2).PasteSpecial _
                Paste:=xlPasteValues
        Application.CutCopyMode = False
    End If

  3. #3
    Forum Contributor
    Join Date
    04-19-2013
    Location
    Yorkshire, England
    MS-Off Ver
    Excel 2010
    Posts
    297

    Re: Slight edit needed with code..

    Brilliant thanks nilem, so simple in the end!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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