Append macro with an IF

[bschulze]

Alright, here we go, I need an append code with a condition that must be met. This is what I have so far:


Dim cRow As Long
Dim DestSheet As Worksheet
Dim SourceSheet As Worksheet
cRow = Sheets("Import").Range("A1").End(xlDown).Row
Set DestSheet = Sheets("Conversion")
Set SourceSheet = Sheets("Import")
For x = 1 To cRow
If SourceSheet.Range("B" & x) = "#N/A" Then
SourceSheet.Range(Cells(x, "A"), Cells(x, "A")).Copy
DestSheet.Range("C65536").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
End If
Next x


The catch is there are going to be multiple "#N/A" with the same value in column A. What I need is something that looks at Conversion sheet to see if any of the "#N/A" exist already. Or code that only pulls data from the first "#N/A" for that particular data.

Example:

Column A Column B
1 data
2 #N/A
3 left
1 good
2 #N/A
3 what
1 now
2 #N/A
3 Who


This code is going to pull all the "2" in column A. I only what it to pull one in. The reason for this is it then appends into a conversion table and I dont want any duplicates for integrity purposes. Any ideas would be great. thanks

 

[Andrew Poulsom]

Try:

Sub Test()
    Dim cRow As Long
    Dim DestSheet As Worksheet
    Dim SourceSheet As Worksheet
    Dim x As Long
    Dim NAData As New Collection
    cRow = Sheets("Import").Range("A1").End(xlDown).Row
    Set DestSheet = Sheets("Conversion")
    Set SourceSheet = Sheets("Import")
    For x = 1 To cRow
        If SourceSheet.Cells(x, 2).Value = "#N/A" Then
            On Error Resume Next
            NAData.Add SourceSheet.Cells(x, 1).Value, CStr(SourceSheet.Cells(x, 1).Value)
            On Error GoTo 0
        End If
    Next x
    For x = 1 To NAData.Count
        DestSheet.Range("C65536").End(xlUp).Offset(1, 0).Value = NAData(x)
    Next x
End Sub

 

[bschulze]

I did not attempt the code before I posted it. Sorry. I stepped through the original code and it and it gave me a type mismatch error (#13) on the first "#N/A" value. I am not sure why. I looked at your code and it make sense. I think it will work once I get this error taken care of. I am getting the "#N/A" value from a vlookup. However I then copy the range and paste values so that it is actually an "#N/A" and not an error from a vlookup. Any ideas on why it is erroring out would be great.

 

[Andrew Poulsom]

I had to change the #N/A values to text entries to get your original code to work (I assumed it had worked for you). Try:

If SourceSheet.Cells(x, 2).Text = "#N/A" Then

 

[bschulze]

Sorry it took me so long to get back to you. Was away. I have installed the code you gave me and it worked like a charm. Thanks for the patience and help....especially for a beginner.


Thanks again