1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Copy Cell If Value Is Not = #N/A

Discussion in 'Business Applications' started by madmaxxx89, Jul 15, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. madmaxxx89

    madmaxxx89 Thread Starter

    Joined:
    Jun 29, 2011
    Messages:
    43
    Hi,

    I am trying to program a Macro which copy's the Value of a Cell to the right cell If it's not #N/A.
    Somehow there is a problem with the expression #N/A. I also tried if <> 0 then Copy but it never worked.
    Maybe there is a basic mistake in the code:


    Code:
    Sub DeleteNA()
        Dim Firstrow As Long
        Dim Lastrow As Long
        Dim Lrow As Long
        Dim CalcMode As Long
        Dim ViewMode As Long
        With Application
            .ScreenUpdating = False
        End With
        'We use the ActiveSheet but you can replace this with
        'Sheets("MySheet")if you want
        With ActiveSheet
            'We select the sheet so we can change the window view
            .Select
            'If you are in Page Break Preview Or Page Layout view go
            'back to normal view, we do this for speed
            ViewMode = ActiveWindow.View
            ActiveWindow.View = xlNormalView
            'Turn off Page Breaks, we do this for speed
            .DisplayPageBreaks = False
            'Set the first and last row to loop through
            Firstrow = 5
            Lastrow = .Cells(.Rows.Count, "C").End(xlUp).Row
     
            'We loop from Lastrow to Firstrow (bottom to top)
            For Lrow = Lastrow To Firstrow Step -1
            'We check the values in the column in this example
                With .Cells(Lrow, "C")
     
    [COLOR=red]            'If IsError(.Value) Then[/COLOR]
    [COLOR=red]            ' GoTo Ende[/COLOR]
     
     
    [COLOR=red]           If .Value <> #N/A Then[/COLOR]
    [COLOR=red]                       ActiveCell.Copy[/COLOR]
    [COLOR=red]                       ActiveCell.Offset(0, 1).Paste[/COLOR]
    [COLOR=red]       Else: GoTo Ende[/COLOR]
     
    [COLOR=red]               End If[/COLOR]
                End With
    Ende:
       Next Lrow
     
        End With
        ActiveWindow.View = ViewMode
        With Application
            .ScreenUpdating = True
            .Calculation = CalcMode
        End With
    End Sub
    In the red section I as playing around. My code is based on this http://www.rondebruin.nl/delete.htm . i used that code to find a empty cell and then delete the row which worked just fine. So i thought I could also use it to find a special value and copy but unfortunatelly I can't get it to work.
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,576
    First Name:
    Hans
    There is a function for this:

    If WorksheetFunction.IsNA(Range("A1")) = True Then

    End If

    Play around with this.
    Happy coding
     
  3. madmaxxx89

    madmaxxx89 Thread Starter

    Joined:
    Jun 29, 2011
    Messages:
    43
    thanks Keebellah! gonna try it soon.
     
  4. madmaxxx89

    madmaxxx89 Thread Starter

    Joined:
    Jun 29, 2011
    Messages:
    43
    I tried it with your Function but I get the error "Run-time error '13': Type mismatch WorksheetFunction" at this line:


    Code:
    Sub DeleteNA()
        Dim Firstrow As Long
        Dim Lastrow As Long
        Dim Lrow As Long
        Dim CalcMode As Long
        Dim ViewMode As Long
        With Application
            .ScreenUpdating = False
        End With
        'We use the ActiveSheet but you can replace this with
        'Sheets("MySheet")if you want
        With ActiveSheet
            'We select the sheet so we can change the window view
            .Select
            'If you are in Page Break Preview Or Page Layout view go
            'back to normal view, we do this for speed
            ViewMode = ActiveWindow.View
            ActiveWindow.View = xlNormalView
            'Turn off Page Breaks, we do this for speed
            .DisplayPageBreaks = False
            'Set the first and last row to loop through
            Firstrow = 5
            Lastrow = .Cells(.Rows.Count, "C").End(xlUp).Row
     
    ' Range("B4").autofill Destination:=Range("B4:B" & Lrow),
     
            'We loop from Lastrow to Firstrow (bottom to top)
            For Lrow = Lastrow To Firstrow Step -1
            'We check the values in the column in this example
                With .Cells(Lrow, "C")
     
                 'If IsError(.Value) Then
                 ' GoTo Ende
     
     
              [COLOR=red]  If WorksheetFunction.IsNA(Range("C5:C" & Lrow)) = True Then[/COLOR]
                    GoTo Ende
                    Else:
                            ActiveCell.Copy
                            ActiveCell.Offset(0, 1).Paste
     
     
                    End If
                End With
    Ende:
       Next Lrow
     
        End With
        ActiveWindow.View = ViewMode
        With Application
            .ScreenUpdating = True
            .Calculation = CalcMode
        End With
    End Sub
     
     
     
    
     
  5. madmaxxx89

    madmaxxx89 Thread Starter

    Joined:
    Jun 29, 2011
    Messages:
    43
    [​IMG] Keebellah [​IMG] [​IMG]
    [​IMG]
    Senior Member with 2,394 posts.
    Join Date: Mar 2008
    Location: Oegstgeest, The Netherlands
    Experience: Never too old to learn!


    47 Minutes Ago, #28
    The problem is that you are applying the function to a whole range and not a sigle cell.

    I change your code.

    Code:
    Code:
    Dim Cell as Range        
    For Each Cell In Range("C" & Firstrow & ":C" & Lastrow)        
    'We check the values in the column in this example            
    'With .Cells(Lrow, "C")              
    'If IsError(.Value) Then             
    ' GoTo Ende             
    If WorksheetFunction.IsNA(Cell.Value) = True Then                
    GoTo Ende            
    Else                
    Cell.Copy                
    Cell.Offset(0, 1).PasteSpecial            
    End If        
    Ende:   Next Cell
    
    Try this and see if it works.
     
  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,576
    First Name:
    Hans
    Something went wrong when posting I think

    Code:
    Dim Cell as Range        
    For Each Cell In Range("C" & Firstrow & ":C" & Lastrow)        
    'We check the values in the column in this example            
    'With .Cells(Lrow, "C")              
    'If IsError(.Value) Then             
    ' GoTo Ende             
    If WorksheetFunction.IsNA(Cell.Value) = True Then                
    GoTo Ende            
    Else                
    Cell.Copy                
    Cell.Offset(0, 1).PasteSpecial            
    End If        
    Ende:   Next Cell
    
    
    Change this part of your code and see if it works
     
  7. madmaxxx89

    madmaxxx89 Thread Starter

    Joined:
    Jun 29, 2011
    Messages:
    43
    Did it. But now there is a mistake in the next line :mad:

    It says "Run-time error '438': Object doesn't support this property method"

    Code:
           
         If WorksheetFunction.IsNA("C1") = True Then
                    GoTo Ende
                    Else:
                            ActiveCell.Copy
    [COLOR=red]                     ActiveCell.Offset(0, 1).Paste[/COLOR]
     
                    End If
                End With
    Ende:
    
    I also tried:

    Code:
    ActiveCell.[B]Offset[/B](rowOffset:=0, columnOffset:=1).Activate
    from the Excel help but it came the same Error
     
  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,576
    First Name:
    Hans
    Code:
    Dim Cell as Range        
    For Each Cell In Range("C" & Firstrow & ":C" & Lastrow)        
    'We check the values in the column in this example            
    'With .Cells(Lrow, "C")              
    'If IsError(.Value) Then             
    ' GoTo Ende             
    If WorksheetFunction.IsNA(Cell.Value) = True Then                
    GoTo Ende            
    Else                
    [B][COLOR="Red"]Cell.Copy                
    Cell.Offset(0, 1).PasteSpecial[/COLOR][/B]            
    End If        
    Ende:   Next Cell
    
    Don't forget to change the part in red too
     
  9. madmaxxx89

    madmaxxx89 Thread Starter

    Joined:
    Jun 29, 2011
    Messages:
    43
    YeeaaaaH!!! It works (y) Keebellah you are the best!
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1007441

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice