0

I have an Excel formula set up from VBA with a IF condition and I want it to return nothing if it's true.

Here is my code:

Set tbl = .ListObjects("Tableau4")

vCherchee = tbl.DataBodyRange(1, 1)

.PivotTables("Tab1").TableRange1.Select
matrice = Selection.Address(ReferenceStyle:=xlA1, RowAbsolute:=True, ColumnAbsolute:=False)

tbl.DataBodyRange(1, 3).Formula = "=IF(OR(ISNA(VLOOKUP([@[Code Frns]]," & matrice & ",3,FALSE)), ISBLANK(VLOOKUP([@[Code Frns]]," & matrice & ",3,FALSE))),1, VLOOKUP([@[Code Frns]]," & matrice & ",3,FALSE))"
tbl.DataBodyRange(1, 4).Formula = "=IF(OR(ISNA(VLOOKUP([@[Code Frns]]," & matrice & ",4,FALSE)), ISBLANK(VLOOKUP([@[Code Frns]]," & matrice & ",4,FALSE))),1, VLOOKUP([@[Code Frns]]," & matrice & ",4,FALSE))"
tbl.DataBodyRange(1, 5).Formula = "=IF(OR(ISNA(VLOOKUP([@[Code Frns]]," & matrice & ",5,FALSE)), ISBLANK(VLOOKUP([@[Code Frns]]," & matrice & ",5,FALSE))),1, VLOOKUP([@[Code Frns]]," & matrice & ",5,FALSE))"
tbl.DataBodyRange(1, 6).Formula = "=IF(OR(ISNA(VLOOKUP([@[Code Frns]]," & matrice & ",6,FALSE)), ISBLANK(VLOOKUP([@[Code Frns]]," & matrice & ",6,FALSE))),1, VLOOKUP([@[Code Frns]]," & matrice & ",6,FALSE))"
tbl.DataBodyRange(1, 7).Formula = "=IF(OR(ISNA(VLOOKUP([@[Code Frns]]," & matrice & ",7,FALSE)), ISBLANK(VLOOKUP([@[Code Frns]]," & matrice & ",7,FALSE))),1, VLOOKUP([@[Code Frns]]," & matrice & ",7,FALSE))"
tbl.DataBodyRange(1, 8).Formula = "=IF(OR(ISNA(VLOOKUP([@[Code Frns]]," & matrice & ",8,FALSE)), ISBLANK(VLOOKUP([@[Code Frns]]," & matrice & ",8,FALSE))),1, VLOOKUP([@[Code Frns]]," & matrice & ",8,FALSE))"
tbl.DataBodyRange(1, 9).Formula = "=IF(OR(ISNA(VLOOKUP([@[Code Frns]]," & matrice & ",9,FALSE)), ISBLANK(VLOOKUP([@[Code Frns]]," & matrice & ",9,FALSE))),1, VLOOKUP([@[Code Frns]]," & matrice & ",9,FALSE))"

Actually it returns 1, but if I don't put anything, it returns 0 and if I put "" it gives me an error. How can I do this? Thanks.

GSerg
  • 76,472
  • 17
  • 159
  • 346
Eraaz
  • 19
  • 4
  • Excel formulas cannot return nothing. `0` and `""` are common things to return, and in case of a `0` you would [hide it with cell formatting](https://stackoverflow.com/q/32169958/11683). Is your problem that you don't know [how to put `""` into a string literal](https://stackoverflow.com/q/9024724/11683)? – GSerg Mar 27 '19 at 12:10
  • Thats exactly what I wanted. Feel idiot to not tried it before. Thanks a lot. – Eraaz Mar 27 '19 at 12:15

1 Answers1

0

So thanks to GSerg. The correct way was.

 tbl.DataBodyRange(1, 5).Formula = "=IF(OR(ISNA(VLOOKUP([@[Code Frns]]," & matrice & ",5,FALSE)), ISBLANK(VLOOKUP([@[Code Frns]]," & matrice & ",5,FALSE))),"""", VLOOKUP([@[Code Frns]]," & matrice & ",5,FALSE))"
Eraaz
  • 19
  • 4