Consulting

Results 1 to 7 of 7

Thread: Solved: problem writing formula via vba

  1. #1
    VBAX Mentor ALe's Avatar
    Joined
    Aug 2005
    Location
    Milan
    Posts
    383
    Location

    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

  2. #2
    VBAX Regular HaHoBe's Avatar
    Joined
    Aug 2004
    Location
    Hamburg
    Posts
    89
    Location
    Hi, ALe,

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

    Ciao,
    Holger

  3. #3
    VBAX Mentor ALe's Avatar
    Joined
    Aug 2005
    Location
    Milan
    Posts
    383
    Location
    I tried but it gives the same problem

  4. #4
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Hi Ale,
    With Index and Match, it works for me. Can't think of any suggestions. Regards
    MD
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  5. #5
    VBAX Regular HaHoBe's Avatar
    Joined
    Aug 2004
    Location
    Hamburg
    Posts
    89
    Location
    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

  6. #6
    VBAX Mentor ALe's Avatar
    Joined
    Aug 2005
    Location
    Milan
    Posts
    383
    Location
    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

  7. #7
    VBAX Mentor ALe's Avatar
    Joined
    Aug 2005
    Location
    Milan
    Posts
    383
    Location
    MD, can you mark this thread as solved for me?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •