VLOOKUP only if #N/A found

[Nicolaf]

Hi,

I would like to do a macro that looks for name in Sheet1 column A then looks for it in Sheet2 column C and if it finds it then copies text in column D (Sheet1) and pastes it next to name found in Sheet2 but ONLY if in such cell (in Sheet2 column D) there is the text #N/A otherwise it does not paste anything.

So if we have name Apple in Cell A7 Sheet1 then the macro will copy cell D7 Sheet1 and copy it in Sheet2 when it finds Apple (in Column C) and pastes it next to it so if Apple is in Cell C8 (Sheet2) then it will paste name in Cell D8 (Sheet2).

How do I do that?

:think::think:

[Bob Phillips]

Public Sub PostErrorDetails()
Dim cell As Range
Dim idx As Long

With Worksheets("Sheet1")

Set cell = .Columns("A").Find("Bob")
If Not cell Is Nothing Then

On Error Resume Next
idx = Application.Match(cell.Value, Worksheets("Sheet2").Columns("A"), 0)
On Error GoTo 0
If idx > 0 Then

If Worksheets("Sheet2").Cells(idx, "D").Text = "#N/A" Then

Worksheets("Sheet2").Cells(idx, "D").Value = cell.Offset(0, 3).Value
End If
End If
End If
End With

Worksheets("Sheet2").Select
End Sub

[Nicolaf]

Great Thanks.

Is there a way to make it more dynamic so instead of just finding "Bob", to look for all names found in range A7:A40 Sheet1 in Range C7:C40 Sheet2 and then do the copy paste in Cell D7:D40 Sheet2 if condition is met?

Nic

[lecxe]

Hi Nic

Try:



Sub Test()
Dim rS1A As Range, rS2C As Range, r As Range, rFound As Range

Set rS1A = Worksheets("Sheet1").Range("A7:A40")
Set rS2C = Worksheets("Sheet2").Range("C7:C40")

For Each r In rS1A
If r <> "" Then
Set rFound = rS2C.Find(r.Value, LookAt:=xlWhole, LookIn:=xlValues)
If Not rFound Is Nothing Then
If rFound.Offset(, 1).Text = "#N/A" Then
rFound.Offset(, 1).Value = r.Offset(, 3).Value
End If
End If
End If
Next r

End Sub

[david000]

What do you really want to happen when an #N/A is found?

I would like to do a macro that looks for name in Sheet1 column A then looks for it in Sheet2 column C and if it finds it then copies text in column D (Sheet1) and pastes it next to name found in Sheet2 but ONLY if in such cell (in Sheet2 column D) there is the text #N/A otherwise it does not paste anything.

How are you looking for something with #N/A in this second paragraph?

So if we have name Apple in Cell A7 Sheet1 then the macro will copy cell D7 Sheet1 and copy it in Sheet2 when it finds Apple (in Column C) and pastes it next to it so if Apple is in Cell C8 (Sheet2) then it will paste name in Cell D8 (Sheet2).







Sub ColDMatchCfromA()
Dim cel As Range
With Sheet2
For Each cel In Range(Cells(1, 3), Cells(Rows.Count, 3).End(xlUp))
cel.Offset(, 1) = Sheet1.Columns(1).Find(cel).Offset(, 3).Value
Next
End With
End Sub

[Nicolaf]

WOW works really well thanks a lot!

:hi::hi: