• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

HELP please: If cell =value then copy entire row to new workbook

Jessica.Charlotte

Jessica.Charlotte

New Member
Hello,

I am having the hardest time trying to get a VBA macro that will work for the following task:
If any cell in $E:$E has value="NO" and value="N/A" then the entire row of this cell is copied to another workbook that already exists (on a sharepoint).

Is this possible and can anyone help me??

I would be very greatful!

Thanks in advance,
-Jessica
 
hmmm.... that sounds tricky!!

So you want it to look at Col.E and if any cell contains "no" OR "N/A" you want that entire row copied? you originally said "If any cell in $E:$E has value="NO" and value="N/A" then the entire row of this cell is copied". I'm just wanting to confirm the criteria...

I'm also assuming that there is a particular workbook on sharepoint that you're referring to?

if you have some VBA started it may be helpful to the VBA Masters to see what you have so far.
 
Hi Jason,

Thanks for your response!
This [once] worked to paste rows with value "NO" onto the next sheet...

Code: 
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim nxtRow As Integer
'Determine if change was to Column F (6)
If Target.Column = 6 Then
'If Yes, Determine if cell = NO
If Target.Value = "NO" Then
'If Yes, find next empty row in Sheet 2
nxtRow = Sheets(2).Range("F" & Rows.Count).End(xlUp).Row + 1
'Copy changed row and paste into Sheet 2
Target.EntireRow.Copy _
Destination:=Sheets(2).Range("A" & nxtRow)
End If
End If
End Sub

But now nothing's working.
 
I'm not strong in VBA, so forgive me if i'm way off.... but the code you posted is searching for Col. F, right? if we want this to work on Col. E, wouldnt you need to change the "If Target.Column = 6 Then" part to say If Target.Column = 5 Then"? Col. 5 = E....

again, i could be shooting hoops out in left field with this....(likely)
 
Hi, Jessica.Charlotte!

As a new user you might want (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, as json said your code pointed to column F, 6th, and only for string values of "NO". If you actually want to be column E, 5th, and values of string "NO" and error N/A, try using the following code.
Code: 
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim nxtRow As Integer, b As Boolean
    'Determine if change was to Column E (5)
    If Target.Column = 5 Then
        'If Yes, Determine if cell = NO or N/A
        If IsError(Target.Value) Then
            b = True
        Else
            If Target.Value = "NO" Then
                b = True
            Else
                b = False
            End If
        End If
        If b Then
            'If Yes, find next empty row in Sheet 2
            nxtRow = Sheets(2).Range("E" & Rows.Count).End(xlUp).Row + 1
            'Copy changed row and paste into Sheet 2
            Target.EntireRow.Copy _
            Destination:=Sheets(2).Range("A" & nxtRow)
        End If
    End If
End Sub

Just advise if any issue.

Regards!
 
Hi Jessica ,

VBA is merely logic , with a structured way of communicating that logic to a computer.

If your code was working , the only changes required to get what you have described are :

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim nxtRow As Integer
'Determine if change was to Column E (5)
If Target.Column = 5 Then
'If Yes, Determine if cell = NO or cell = "N/A"
If Target.Value = "NO" Or Target.Value = "N/A" Then
'If Yes, find next empty row in Sheet 2
nxtRow = Sheets(2).Range("F" & Rows.Count).End(xlUp).Row + 1
'Copy changed row and paste into Sheet 2
Target.EntireRow.Copy Destination:=Sheets(2).Range("A" & nxtRow)
End If
End If
End Sub

If the above does not work , we can go through the corrections required to make it work.

Narayan
 
NARAYANK991 said:
If Target.Value = "NO" Or Target.Value = "N/A" Then
Click to expand...
@NARAYANK991
Hi!
Am I missing something or the above sentence works only for cells that contains a string value of "N/A" and not for cells containing an error value of NA()?
I faced this many time before but just in case I just tested again and I keep getting the same error 13 Type Mismatch. Does it work for you?
Regards!
 
Hi Pablo ,

The OP has posted "N/A" ; I do not know whether that is a text string or an error value. Let us wait for her to confirm what she means.

Narayan
 
@NARAYANK991
Hi!
I agree with you. But having found that f_____g (fooling, remember? ;) ) error 13, I always use to avoid it with a previous IsError function first. Thus any N/A possible case is handled.
Regards!
 
You must log in or register to reply here.
Back
Top