Pasted cells contain #N/A

0
votes

When I copy cells from one column only the pasted cells always have #N/A rather than the text they should have.

This happens when I either;

  • Copy a range of cells including any in this one affected column (Column A)
  • Copy a range of cells from only in the affected column
  • Delete a row (all values on the next row for the entire length of the row then have #N/A
  • Copy more than one cell from not in the affected column and paste any of them into the affected column

If I however copy a single cell from the affected column is pastes as it should. If I copy multiple cells from the affected column and paste them elsewhere in the sheet they paste OK.

I have zero idea how to solve this and searching on google did not help find an answer.

The cells in the affected column (Column A) are either standard text or are cells with a drop down list data validation type; None have formulas.

EDIT -

I have narrowed it down to the following code. The code is to change the value the user selects in the drop down list to something else.

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice 20161026
    selectedNa = Target.Value
    If Target.Column = 1 Then
        selectedNum = Application.VLookup(selectedNa, 
Worksheets("refList_Hidden").Range("table_dropDown"), 2, False)
        If Not IsError(selectedNum) Then
            Target.Value = selectedNum

        End If
End If
End Sub

I'm pretty sure the answer is to check if the range contains only one cell, if it does then run the VLOOKUP, if it contains more than one cell then then do not run the above code. Does anyone know how to do this?

2
excel
Do the cells you are copying contain formulae? If they do then you can try using paste special with values... – Solar Mike
Only ideas... How is the drop-down list data linked and do you have any custom format cells? But best would be to use Solar Mike's advice! – Wiz
There are no formulas in column A. Only text and drop downs. The drop downs reference a named range on a different sheet. The #N/A's appear even if I go down below all of my data and copy and paste empty cells. Even selecting 'Paste Formatting' results in the #N/A being pasted. – ChrisBull
This problem is very frustrating as I can't insert a row without the entire row (10,000 odd columns) being populated with #N/A which I then have to delete! – ChrisBull

2 Answers

0
votes

As further explanation to @SolarMikes comment -

If range A1:A3 contains A, B and C respectively this formula in B1:
=MATCH("A",A1:A3,0) will return 1.

If you then copy cell B1 (including the formula) to cell C1 the formula will update to:
=MATCH("A",B1:B3,0) - there are no matches in that range so it returns #N/A.

To work around this I'd suggest using absolute cell referencing.
That is change A1:A3 to $A$1:$A$3, or as Mike suggested, copy and paste values.

0
votes

In the code above i just needed to ensure there was only one cell in the range rather than lots;

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice 20161026
    selectedNa = Target.Value
If Target.Column = 1 And Target.Rows.Count = 1 And Target.Columns.Count = 1 Then
    selectedNum = Application.VLookup(selectedNa, Worksheets("refList_Hidden").Range("table_dropDown"), 2, False)
    If Not IsError(selectedNum) Then
        Target.Value = selectedNum

    End If
End If
End Sub