vba using cell reference in formula?

Question

I am trying to insert a formula into a cell in a row in my excel spread sheet. I am doing this from outlook, by getting the content of an email and extracting it into excel using vba.

I am getting the text from my email body that comes after 'Description of provisional supplier:' and inserting this into cell 4 in my row in excel, I then want to insert in the row, cell 5 a formula which references cell 4.

For some reason I get a application defined or object defined error on the line where my formula is

Dim b7 As String
 If TypeName(olkMsg) = "MailItem" Then
    b7 = olkMsg.Body

    Dim indexOfNameb4 As Integer
        indexOfNameb4 = InStr(UCase(b7), UCase("Description of the provisional Supplier:"))


    Dim indexOfNamec4 As Integer
       indexOfNamec4 = InStr(UCase(b7), UCase("Current Status: "))

    Dim finalStringb4 As String

        finalStringb4 = Mid(b7, indexOfNameb4, indexOfNamec4 - indexOfNameb4)

        LResult3364 = Replace(finalStringb4, "Description of the provisional Supplier:", "")

        excWks4.Cells(intRow4, 4) = LResult3364

        lastcell = excWks4.Cells(intRow4, 4)

        excWks4.Cells(intRow4, 5) = "=IF(ISERROR(INDEX('Look Up Supplier Sheet'!$A$1:$G$10004,SMALL(IF(LEFT('Look Up Supplier Sheet'!$B$1:$B$10004,5)=LEFT(" & lastcell & ",5),ROW($B$1:$B$10004)),ROW(1:1)),6)),"",INDEX('Look Up Supplier Sheet'!$A$1:$G$10004,SMALL(IF(LEFT('Look Up Supplier Sheet'!$B$1:$B$10004,5)=LEFT(" & lastcell & ",5),ROW($B$1:$B$10004)),ROW(1:1)),6))"



End If

ttaaoossuuuu ttaaoossuuuu · Accepted Answer · 2014-11-12T09:19:51

Use lastcell = excWks4.Cells(intRow4, 4).Address and excWks4.Cells(intRow4, 5).Formula = ... instead.

Plus "" (double quotes) in your formula actually result in a single quote in cell. Use """" (quadruple quotes) to insert empty quotes in cell.

vba using cell reference in formula?

1 Answers