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
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