VBA: type mismatch error with FunctionaArray

Question

Getting a type mismatch(run time error 13) on this code and I am unable to solve it.

LastWeek = 10
ActiveCell.FormulaR1C1 = LastWeek + 1
Range(Cells(ActiveCell.Row + 1, ActiveCell.Column), Cells(ActiveCell.Row + 1, _
        ActiveCell.Column)).FormulaArray _
        = "=INDEX(R2C5:R81C10, MATCH(R4C12&R4C13&R3C15,R1C2:R81C2&R1C3:R81C3&R1C4:R81C4,0)-1,MATCH(R4C14,R1C5:R1C10,0))" / "=INDEX(R2C5:R81C10, MATCH(R4C12&R4C13&R3C15,R1C2:R[72]C2&R1C3:R[72]C3&R1C4:R[72]C4,0)-1, 1)" * 100

I am stuck with this part for a long time now and out of clue to solve it. Any help will be highly appreciated.

Alexey C · Accepted Answer · 2018-11-21 12:20:33Z

2

Why u use quotes in formula text before and after "/" and "*"?

You looking for this formula?

Range(Cells(ActiveCell.Row + 1, ActiveCell.Column), Cells(ActiveCell.Row + 1, _
        ActiveCell.Column)).FormulaArray _
        = "=INDEX(R2C5:R81C10, MATCH(R4C12&R4C13&R3C15,R1C2:R81C2&R1C3:R81C3&R1C4:R81C4,0)-1,MATCH(R4C14,R1C5:R1C10,0))/INDEX(R2C5:R81C10, MATCH(R4C12&R4C13&R3C15,R1C2:R[72]C2&R1C3:R[72]C3&R1C4:R[72]C4,0)-1, 1) * 100"

answered Nov 21, 2018 at 12:20

Alexey C

3041 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

surajitM Over a year ago

It is basically formula1 / formula2 * 100. If I keep only formula1 or formula2 (including the quotes) alone, it seem to populate the desired cell with the value. However, on trying to use both the formula's together, it is giving me the error.

surajitM Over a year ago

Thanks @Alexey C. Thats what I wanted. :)

Collectives™ on Stack Overflow

VBA: type mismatch error with FunctionaArray

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related