PDA

View Full Version : Solved: problem writing formula via vba



ALe
03-24-2006, 03:46 AM
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


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



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

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

HaHoBe
03-25-2006, 12:24 AM
Hi, ALe,

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

Ciao,
Holger

ALe
03-25-2006, 02:20 AM
I tried but it gives the same problem

mdmackillop
03-25-2006, 03:19 AM
Hi Ale,
With Index and Match, it works for me. Can't think of any suggestions. :( Regards
MD

HaHoBe
03-25-2006, 04:41 AM
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

ALe
03-25-2006, 07:48 AM
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

ALe
03-25-2006, 07:50 AM
MD, can you mark this thread as solved for me?