CODEWITHSUNDEEP
excelvbaformula
jo phul
jo phul

Reputation: 647

Excel VBA building a formula from variables

My apologies for such an easy question. I have googled this to death, and I can't figure out why this won't work. I just want to add a new row and insert a formula into a cell.

My code is this

Private Sub CommandButton1_Click()

    Dim lRow As Long
    Dim lStr As String
    Dim nRow As Long
    Dim nStr As String

    lRow = Cells(Rows.Count, 5).End(xlUp).Row
    lStr = CStr(lRow)
    nRow = lRow + 1
    nStr = CStr(nRow)

    Cells(nRow, 1).Value = Date
    Cells(nRow, 2).Value = ""
    Cells(nRow, 3).Value = 25
    Cells(nRow, 4).Value = 53
    Cells(nRow, 5).Select
    ActiveCell.formula = "=IF(AND(C" & nRow & "=""""|D" & nRow & "="""")|""""|E" & lRow & "-C" & nRow & "+D" & nRow & ")"

End Sub

I've tried using the string value of the row in the formula (using nStr instead of nRow, etc).

If I remove the "=" sign at the beginning and put it as a value, it inserts, and if I then add the "=" via excel it evaluates fine, so I know the format of the formula as a string is fine.

But why won't it insert the formula?

I've also tried doing it in r1c1 notation.

Any ideas?

Note, the values 25 and 53 are temporary values. The end user will be manually entering values into those cells.

Upvotes: 1

Views: 1358

Answers (1)

jo phul
jo phul

Reputation: 647

Turns out that even though 

=IF(AND(C6=""|D6="")|""|E5-C6+D6)

works if you manually enter it into a cell, through VB you need to replace the pipe symbol with a comma. So changing my formula to 

ActiveCell.formula = "=IF(AND(C" & nRow & "="""",D" & nRow & "=""""),"""",E" & lRow & "-C" & nRow & "+D" & nRow & ")"

solved the problem

Upvotes: 1

Related Questions