Formula with Cell showing: 'E3'

[Sibrulotte]

Hi,
I'm using a macro that lays this formula in F3:

=CONCATENATE(RIGHT(LEFT(List_1!E3,7),4),"-",RIGHT(LEFT(List_1!E3,9),2))

But the macro runs and outputs 'E3' which returns a #value.
(there's an extra ' before and after the E3.)

How does that happen?

[shg]

That's a formula, not a macro.

What's the result showing in E3 when the error occurs?

[Cutter]

Your formula in a simpler way would be:

=MID(List_1!E3,4,4)&"-"&MID(List_1!E3,8,2)

[Sibrulotte]

This is what I have in E3:

000115910 - BAKELS PLATINUM


And F3 where my formula is laid down should return:
1159-10

Thanks cutter: i'll try that.

I know it's a formula, but it's running it through the macro that inserts the appostrophes. Whithin the coding the formula doesn'T have it.

[Sibrulotte]

I'm sorry I didn't communicate my issue properly.

[DonkeyOte]

You must double up quotations if inserting literally within a string, eg along the lines of:

.Formula = "=TEXT(MID(List_1!E3,4,6),""0000-00"")"

[Sibrulotte]

Range("F3").Select
    ActiveCell.FormulaR1C1 = _
        "=TEXT(MID(List_1!E3,4,6),""0000-00"")"
    Range("F3:F999").Select
    Selection.FillDown

And it still brings me back the 'E3' with appostrophes.
Bummed.

Thank you very much for the cues guys.
I'll keep looking on the googlers.

[DonkeyOte]

You're using .FormulaR1C1 but not R1C1 notation

Either

a) use .Formula

or

b) use R1C1 notation in the formula itself:

"=TEXT(MID(List_1!RC[-1],4,6),""0000-00"")"

[Sibrulotte]

Lovely,
I I'll try to learn more on the .formulaR1C1 notation, as I'm clueless about the difference.

Thank you so much!

[DonkeyOte]

I added a post sometime ago that goes through the basics if interested: http://www.excelforum.com/2171545-post2.html