Gos-C
Active Member
- Joined
- Apr 11, 2005
- Messages
- 258
- Office Version
- 365
- 2016
- Platform
- Windows
Hi all,
This is a crossed-post (see http://www.excelforum.com/excel-programming/654825-copy-and-paste-cells-if-match-found.html) as I am still unable to eliminate the #N/A from the following code:
When I entered the first cheque number (or a subsequent one that hasn't been previously entered), #N/A is being filled in C, D, E, F, G, H, L, N, and O. If the cheque number entered is not found, I want C, D, E, F, G, H, L, N, and O to be blank.
I think there needs to be:
Can someone help me, please.
Thank you,
Gos-C
This is a crossed-post (see http://www.excelforum.com/excel-programming/654825-copy-and-paste-cells-if-match-found.html) as I am still unable to eliminate the #N/A from the following code:
Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.ScreenUpdating = False
Dim dataRange As Range, formulastr As String
With Target
If 1 < .Row And .Column = 2 And .Cells.Count = 1 Then
Application.EnableEvents = False
Set dataRange = Range(.Offset(-1, 0), .Parent.Range("B1:N1"))
formulastr = "VLOOKUP(" & .Address(, , xlR1C1) & "," & dataRange.Address(, , xlR1C1) & ",COLUMN()-" & .Column & "+1,FALSE) & """" "
With dataRange.EntireColumn.Rows(.Row)
With .Offset(0, 1).Resize(, .Columns.Count - 1)
.FormulaR1C1 = "=" & formulastr
.Value = Evaluate("=IF(" & .Address(, , , True) & "=0,0," & .Address(, , , True) & ")")
On Error Resume Next
.SpecialCells(xlCellTypeConstants, xlErrors).ClearContents
On Error GoTo 0
End With
.EntireRow.Range("I1,J1,K1,M1").ClearContents
End With
End If
End With
If Target.Cells.Count > 1 Then
Exit Sub
End If
On Error GoTo ErrHandler:
If Not Application.Intersect(Range("D:D,H:H"), Target) Is Nothing Then
If IsNumeric(Target.Value) = False Then
Application.EnableEvents = False
Target.Value = StrConv(Target.Text, vbProperCase)
Application.EnableEvents = True
End If
End If
ErrHandler:
Application.EnableEvents = True
Application.ScreenUpdating = True
End SubWhen I entered the first cheque number (or a subsequent one that hasn't been previously entered), #N/A is being filled in C, D, E, F, G, H, L, N, and O. If the cheque number entered is not found, I want C, D, E, F, G, H, L, N, and O to be blank.
I think there needs to be:
Code:
If IsError . . . ThenCan someone help me, please.
Thank you,
Gos-C
Last edited:
