Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2005

    Unanswered: assigning a string variabel to a cell formular

    I've been searching the forums for a while, finding similar problems, but none solving my exact situation.

    Observe the below code snippet, and focus on the string variabel formeltekst, which I would later like to the formula of a specific cell: a.offset(0, taloffset).

    But I get an error 1004.

    An example of how the formeltekst string would look "=2731,21+2731,23+897,95+975,25".

    its a user request that the formula is visible, which is why I dropped and commented out the verdi variable that would otherwise just add the sum/value of the formula instead.

    Any suggestions? (apart from shooting the user demanding to be able to see the formula?)

    Cheers, Trin

        For Each a In Selection
            kommando.Parameters.Item(0) = aarstal
            kommando.Parameters.Item(1) = a.Value
            Set GIDrs = kommando.Execute
            While GIDrs.EOF <> True
                verdi = verdi + GIDrs![Beløb (DKK)]
                tekst = tekst & GIDrs![Beløb (DKK)] & " Kr som forfaldt: " & Format(GIDrs![Forfaldsdato], "mmm, yyyy") & vbNewLine
                formeltekst = formeltekst & "" & GIDrs![Beløb (DKK)] & "+"
            formeltekst = Left(formeltekst, Len(formeltekst) - 1)
            formeltekst = "=" & formeltekst
            a.offset(0, taloffset).Formula = formeltekst
            'a.offset(0, taloffset).Value = verdi
            a.offset(0, taloffset).AddComment
            a.offset(0, taloffset).Comment.Visible = False
            a.offset(0, taloffset).Comment.Text Text:="" & tekst & ""
            a.offset(0, taloffset).Comment.Shape.TextFrame.AutoSize = True
            verdi = 0
            tekst = ""
            formeltekst = ""
            Set GIDrs = Nothing
        Next a
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  2. #2
    Join Date
    Sep 2008
    London, UK
    Hi Trin,

    Which line throws the error and what is the error number/message?

    I tested your formula string like this ( "." rather than "," is the decimal separator in my locale) :
    Sub foo()
        Const strFORMULA As String = "=2731.21+2731.23+897.95+975.25"
        Range("A1").Formula = strFORMULA
    End Sub
    And it worked fine.

Posting Permissions

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