range.formula = ... Excel inserts a "@" and breaks how I want the formula to act

Question

I am trying to code a vba action that results in a Spilling formula.

ActiveSheet.Range("B1").Formula = "=NUMBERVALUE(" & topStore.Worksheet.Name & "!" & topStore.Address & ":" & botStore.Address & ")"

As a string variable, the value is

=NUMBERVALUE(Shipping!$A$2:$A$3592)

But when it lands in B1 it always has a @ in it and it results in a #VALUE! error.

=NUMBERVALUE(@Shipping!$A$2:$A$3592)

Is there a setting that I can disable or something I can add to the command that would prevent the @?

score 1 · Accepted Answer · answered Oct 31 '20 at 13:26

1

replace:

.Formula

with:

.Formula2

answered Oct 31 '20 at 13:26

Gary's Student

I can't believe I spent over an hour trying to Google an answer and the solution is this simple. Thank you – Daniel Piechowski Oct 31 '20 at 13:31
@DanielPiechowski I STILL don't know when `.Formula2` is required. If you find any info, Please update your question. – Gary's Student Oct 31 '20 at 13:39
1

@Gary'sStudent https://stackoverflow.com/a/61138478/11683? – GSerg Oct 31 '20 at 13:56

1 Answers1