assigning a string variabel to a cell formular

Trinsan · #1 (**permalink**) 09-27-11, 05:58

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

Code:

    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)] & "+"
            GIDrs.MoveNext
        
        Wend
        
        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

Colin Legg · #2 (**permalink**) 09-27-11, 16:51

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

Code:

Sub foo()
    Const strFORMULA As String = "=2731.21+2731.23+897.95+975.25"
 
    Range("A1").Formula = strFORMULA
 
End Sub

And it worked fine.

Internet Services

Web Development

Digital Marketing

Consumer Tech