Pick the left cell value if N/A found on Right cell.

[Nishu]

I have a data where if #n/a found on 2nd cell, then 1st cell value will replace the 2nd cell value. below code is working fine but whenever any blank value is in 1st cell code skip that steps and #n/a remains as it is.
Please assist

Sub NAFILL()
For Each r In Columns(1).SpecialCells(2)
If InStr(r.Offset(0, 1).Text, "#N/A") > 0 Then
r.Offset(0, 1).Value = r.Value
End If
Next r
End Sub

 

[p45cal]

For Each r In Union(Columns(1).SpecialCells(2), Columns(1).SpecialCells(4))

 

[shrivallabha]

Why are you not checking in column 2 directly?

Sub NAFILL()
For Each r In Columns(2).SpecialCells(2, 16)
  If InStr(r.Text, "#N/A") > 0 Then
  r.Value = r.Offset(0, -1).Value
  End If
Next r
End Sub

 

[Nishu]

Why are you not checking in column 2 directly?

Sub NAFILL()
For Each r In Columns(2).SpecialCells(2, 16)
  If InStr(r.Text, "#N/A") > 0 Then
  r.Value = r.Offset(0, -1).Value
  End If
Next r
End Sub

Yes, you are right. We can also do same from Column 2, Just one qs. what exact "SpecialCells(2, 16)" is doing ?

 

[NARAYANK991]

Yes, you are right. We can also do same from Column 2, Just one qs. what exact "SpecialCells(2, 16)" is doing ?

Hi ,

While you are in the Visual Basic Editor screen , just press F1 to bring up the Help system , and enter SpecialCells ; you can check out the help on :

Range.SpecialCells Method

Narayan