Solved: problem writing formula via vba

here's the problem.

I create a string that will be pasted in the activecell as its formula.
The formula is correct, but excel display error message until I press "F2" or I hit enter.

Two questions.
- Why?
- How to solve it?

Thanks

Here the sub

[vba]
Sub InsVMatrix(Matrix As Range, RowVal As Range, ColVal As Range)
On Error GoTo fine
Dim myrow As String, mycol As String
Dim MaAddr As String, RowValAddr As String, ColValAddr As String
MaAddr = Matrix.Address
myrow = Matrix.Columns(1).Address
mycol = Matrix.Rows(1).Address
RowValAddr = RowVal.Address
ColValAddr = ColVal.Address
ActiveCell.Formula = "=INDICE(" & MaAddr & ",CONFRONTA(" & RowValAddr _
& "," & myrow & ",0),CONFRONTA(" & ColValAddr & "," & mycol & ",0))"
fine:
End Sub


Sub TryIt()
Call InsVMatrix(Range("a1:d4"), Range("a2"), Range("b4"))
End Sub

[/vba]

Suppose to have data in range ("a1:d4")

for english version translate "indice" with index and "confronta" with match

Hi, ALe,

please use FormulaLocal instead of just Formula which would expect the formula to be set in English.

Ciao,
Holger

I tried but it gives the same problem

Hi Ale,
With Index and Match, it works for me. Can't think of any suggestions. :( Regards
MD

Hi, ALe,

I can?t even get my Excel to write any information using FormulaLocal although both debug.print and a messagebox show the propper formula in local notation. This even remains after I rebooted my computer - can?t get the propper syntax (to my opinion) to work: tried it with address, changed to FormulaR1C1 and ReferenceStyle xlR1C1. If there is a match it will occur in the cell specified by row and column not within the rest.

And, like MD stated, no problem using the english terms.

Ciao,
Holger

Thank you. Now I solved translating the formula from italian to english. Now it works, I don't know why it didn't with formulalocal. I'll tell you as soon as I understand the reason.

Bye, thanks again:thumb

MD, can you mark this thread as solved for me?