-2

I copied precisely the codes from MSDN: sample-user-defined-function-to-hide-formula-errors-in-excel

The formula I'm trying to put in a range of cells is:

  Selection = "=IFError(INDEX(Data_Import!$B$2:$R$16, MATCH(Reg!$B4,     
   Data_Import!$A$2:$A$16,0),Reg!C$3), "n/a" )"

But I am getting an error message in the syntax

Compile error:

Expected: end of statement

what am I doing wrong?

my sub code is:

Sub PresentDat(Wks As Integer)
Dim x, LenWks As Long
Dim Y As Variant
Worksheets(5).Activate
    Range("B26").CurrentRegion.Select
        x = Selection.Columns.Count - 1
Worksheets(Wks).Activate
    Sheets(Wks).UsedRange.Find("Element").Select
        LenWks = Range(Selection, Selection.End(xlDown)).Rows _
            .Count - 1 Selection.Offset(1, 1).Resize(LenWks, x).Select
Selection.Formula = "=INDEX(Data_Import!$A$1:$R$65,     
MATCH($B23,Data_Import!$A$1:$A$65,0), COLUMN(C23)-1)"
    Sheets(Wks).UsedRange.Find("No.").Select
        LenWks = Range(Selection, Selection.End(xlDown)).Rows _
            .Count - 4
Selection.Offset(1, 1).Resize(LenWks, x).Select
Selection = "=IFError(INDEX(Data_Import!$B$2:$R$16, MATCH(Reg!$B4, Data_Import!$A$2:$A$16,0),Reg!C$3), )"

End Sub

I just can't get it to put in the error message I want in the cells, :c . . Thanks!

Community
  • 1
  • 1
Reverus
  • 133
  • 9
  • by "replace" I am referring to the second arguement of the "iferror" worksheet function: iferror( function, replace with if the error happens) – Reverus Dec 23 '17 at 23:51

1 Answers1

1

In terms of the top formula shown you need to escape the inner "" with "" so the entire formula doesn't thrown an error i.e. ""n/a""

= "=IFError(INDEX(Data_Import!$B$2:$R$16, MATCH(Reg!$B4,Data_Import!$A$2:$A$16,0),Reg!C$3), ""n/a"")"
QHarr
  • 83,427
  • 12
  • 54
  • 101
  • trying now... OMG YOU ARE A GENIUS! this worked, ty so much! So, if you need to place " " in side a string you just double it up? "" "" Nice. – Reverus Dec 23 '17 at 23:55
  • I'm still kinda a noob with a "Excel 2016 Power Programming with VBA" book ;) but sometimes google + book = no answers :C – Reverus Dec 23 '17 at 23:57
  • 1
    fwiw, you can avoid doubling up double quotes (e.g. `""` or an empty string) within a quoted string by using `TEXT(,)`. In other words, replace `""""` with `TEXT(,)`. –  Dec 24 '17 at 00:21
  • @Jeeped I did not know that. I will be adding that to the armoury. – QHarr Dec 24 '17 at 06:48