Doubt in VBA Excel 2013

Asked

Viewed 50 times

2

I have this bit of code: 

  Range("F8").Select

  For Each m In Range(Range("C8"), Range("C" & rows.Count).End(xlUp))

     If ActiveCell.Offset(0, 0) <> " " Then
        ActiveCell.Offset(0, 7) = Application.vlookup(ActiveCell, Sheets("Chargeback code-Pursuit").Range("A2:B20000"), 2, False)
        ActiveCell.Offset(1, 0).Select
     End If

  Next

And when doing this the desired data appears, but qd cell C8 is empty the field that is filling with vlookup is showing as #N/A.

How do I make Cells that contain #N/A become Blank Cells?

Below is the #N/A example

Thanks for the help.

inserir a descrição da imagem aqui

database excel vba

1 answer

3


you can add the formula IFERROR:

ActiveCell.Offset(0, 7) = Application.iferror(Application.vlookup(ActiveCell, Sheets("Chargeback code-Pursuit").Range("A2:B20000"), 2, False),"")

the

ActiveCell.Offset(0, 7) = Application.vlookup(ActiveCell, Sheets("Chargeback code-Pursuit").Range("A2:B20000"), 2, False)
If IsError(ActiveCell.Offset(0, 7)) Then ActiveCell.Offset(0, 7) = ""

  • Thanks for the help, but this not working @virtualdvid gives sub or Function not defined error :S

  • Sorry my mistake, the problem is because the vlookup is not a VBA resource. I corrected my answer, tell me if it works :)

  • To do this I have to put the function on a certain sheet? Because currently of the error of Unable to get the Vlookup Property of the Worksheetfunction class

  • Try the Application.WorksheetFunction.VLookup(arg1,arg2,arg2,[arg4])

  • You want the formula or just the value in the cell?

  • As it is in the code that I showed works, only that I can not avoid the #N/A and that is only what I intend to do.

  • You try the first line of my answer? Application.iferror(Application.vlookup(

  • Yes, and gave Sub or Function not defined error

  • I ran that line was missing Application. in the vlookup you can try again with this line. I am also working on another possible response. I will update my response soon.

  • The second option does not pass here Then Activecell.Offset(0, 7) = " " is normal? continues to give #N/A

  • 1

    I was able to resolve: [ For Each M In Range(Range("C8"), Range("C" & Rows.Count).End(xlUp)) Activecell.Offset(0, 7) = Application.vlookup(Activecell, Sheets("Chargeback code-Pursuit").Range("A2:B20000"), 2, False) Activecell.Offset(1, 0). Select If Iserror(Activecell.Offset(0, 7)) Then Activecell.Offset(0, 7) = "" Next Cells.Replace "#N/A", """, xlWhole]

Show 6 more comments

Browser other questions tagged database excel vba

You are not signed in. Login or sign up in order to post.