@ Sign Causing an Error

[rex759]

Hello,
I created a macro that pastes a formula into a cell. It should look like this:
=IFERROR(INDEX(Physical_Sample!$A$2:$A$17, MATCH(0,COUNTIF($AE$1:AE1,Physical_Sample!$A$2:$A$17), 0)),"")

What happens is Excel automatically adds the “@” sign in front of the second occurrence of the sheet tab name:
=IFERROR(INDEX(Physical_Sample!$A$2:$A$17, MATCH(0,COUNTIF($AE$1:AE1,@Physical_Sample!$A$2:$A$17), 0)),"")

With the @ sign, it doesn’t pull any results. If I remove the @ sign manually, it works fine.

Do I need to do use Substitute to get rid of the @ sign?


Any help is appreciated.

 

[Fluff]

Can you post the line of code that inserts the formula.

 

[Norie]

Can you post the code?

 

[rex759]

'Add Formulas
With Worksheets("Notes")
.Range("AD2") = "=IFERROR(INDEX(Book_Sample!$B$2:$B$" & LastB2 & ",MATCH(0,COUNTIF($AD$1:AD1,Book_Sample!$B$2:$B$" & LastB2 & "),0)),"""")"
.Range("AE2") = "=IFERROR(INDEX(Physical_Sample!$A$2:$A$" & lastb3 & ",MATCH(0,COUNTIF($AE$1:AE1,Physical_Sample!$A$2:$A$" & lastb3 & "),0)),"""")"
.Range("AD2:AE2").Copy
.Range("AD3:AE10").PasteSpecial Paste:=xlFormulas
End With

 

[Fluff]

Thanks for that, try

With Worksheets("Notes")
   .Range("AD2:AD10").Formula2 = "=IFERROR(INDEX(Book_Sample!$B$2:$B$" & LastB2 & ",MATCH(0,COUNTIF($AD$1:AD1,Book_Sample!$B$2:$B$" & LastB2 & "),0)),"""")"
   .Range("AE2:AE10").Formula2 = "=IFERROR(INDEX(Physical_Sample!$A$2:$A$" & lastb3 & ",MATCH(0,COUNTIF($AE$1:AE1,Physical_Sample!$A$2:$A$" & lastb3 & "),0)),"""")"
End With

 

[rex759]

Ok, I had to step out for an hour but will test as soon as I get back and let you know. Thanks for responding

 

[Fluff]

 

[Fluff]

As you have dynamic array functions you could also use

With Worksheets("Notes")
   .Range("AE2").Formula2 = "=unique(Book_Sample!H2:H" & lastb2 & ")"
   .Range("AE2").Formula2 = "=unique(Physical_Sample!$A$2:$A$" & lastb3 & ")"
End With

 

[rex759]

Interesting.... The first solution worked perfectly. The second one (using Unique) worked almost as well. The only difference is it skipped a row after the first entry. I had that issue with my first formula using the countif but solved it by entering a zero in cell AE1 and AD1.

Thank you for the help

 

[Fluff]

You're welcome & thanks for the feedback.