2

I have looked at the other posts around errors when programmatically inserting formulas into Excel cells using VBA. But none of the tricks seem to help with the specific formula I am trying to insert. The .formula action syntax seems ok, since if I use a simple formula like "=3+3", the code executes ok. The formula I want to insert also seems ok, since when I look at it in the Immediate Window and then paste into manually into the Excel cell, it works correctly... any ideas! Here's the code:

Public Sub Populate_Formulae()

    Dim oWrkBk As Workbook
    Dim oWrkSht As Worksheet
    Dim strLineNumber As String
    Dim intCounter As Integer
    Dim rngColB As Range
    Dim rngColA As Range
    Dim rngColD As Range
    Dim strFormula As String
    Dim intCellCounterLo As Integer
    Dim intCellCounterHi As Integer

    Set oWrkBk = Excel.ActiveWorkbook
    Set oWrkSht = oWrkBk.Sheets("WerkBonApp")
    intCellCounterLo = 10 'first source row to check in
    intCellCounterHi = 29 'last source row to check in

    For intCounter = 17 To 362 'start processing the target sheet at row 17 "LIJN C"
        'Determine which row we are parsing by checking column B
        Set rngColB = oWrkSht.Cells(intCounter, 2)

        If InStr(1, rngColB.Text, "LIJN") <> 0 Then
            strLineNumber = Right$(rngColB.Text, 1)
        Else
            'Put the formulae in the cells in column A
            'Set Column A formula
            Set rngColA = oWrkSht.Cells(intCounter, 1) 'get the current cell to populate the formula
            ' create the formula string
            strFormula = "=VLOOKUP(MAX('Lijn " & strLineNumber & "'!I" & intCellCounterLo & ":'Lijn " & strLineNumber & "'!I" & intCellCounterHi & ");'Lijn " _
                & strLineNumber & "'!I" & intCellCounterLo & ":'Lijn " & strLineNumber & "'!J" & intCellCounterHi & ";2;FALSE)"

            'strFormula now equates to:
            ' =VLOOKUP(MAX('Lijn C'!I10:'Lijn C'!I29);'Lijn C'!I10:'Lijn C'!J29;2;FALSE)

            ' this works when manually entered into the Excel cell

            ' but gives error when executed in VBA code:
            ' Run-time error '1004':
            ' Application-defined or object-defined error

            'put the formula into the current cell
            rngColA.Formula = strFormula 'get error message. This function works if strFormula= "=3+3" , for example.

        End If
    Next intCounter
End Sub

Any and all advice very welcome!

Paul

QHarr
  • 83,427
  • 12
  • 54
  • 101
P. S. Williams
  • 43
  • 1
  • 6
  • What is the value of strFormula when this fails? – QHarr Sep 06 '18 at 08:52
  • =VLOOKUP(MAX('Lijn C'!I10:'Lijn C'!I29);'Lijn C'!I10:'Lijn C'!J29;2;FALSE) – P. S. Williams Sep 06 '18 at 09:01
  • I don't think you want Lijn C repeated in this way: 'Lijn C'!I10:'Lijn C'!I29 Should it be more like 'Lijn C'!I10:I29 ? – QHarr Sep 06 '18 at 09:02
  • 1
    Hey guys, did you know you can use ` (backtick) to enclose code in comments? It makes these formulae stand out from ordinary text, which I find helpful. That said, @QHarr, while the syntax seems unusual, Excel doesn't seem to have a problem with the sheet name appearing twice in the cell address. (Which surprised me, tbh.) – Inarion Sep 06 '18 at 09:09
  • Thanks both. I will try simplifying the cell addresses in the code and let you know if it changes anything. Back in touch shortly. – P. S. Williams Sep 06 '18 at 09:24
  • Ok, so the line of code that set the formula string now looks like this: `strFormula = "=VLOOKUP(MAX('Lijn " & strLineNumber & "'!I" & intCellCounterLo & ":I" & intCellCounterHi & ");'Lijn " & strLineNumber & "'!I" & intCellCounterLo & ":J" & intCellCounterHi & ";2;FALSE)"` which equates to: =VLOOKUP(MAX('Lijn C'!I10:I29);'Lijn C'!I10:J29;2;FALSE) Which gives the correct value when manually pasted into the cell in Excel. However the code still cannot insert the formula into the cell without the runtime error. – P. S. Williams Sep 06 '18 at 09:34
  • The end of the string concatenation looks odd to me: `intCellCounterHi & ";` The ampersand plus the "hanging" quotation mark? Try removing those? – Cindy Meister Sep 06 '18 at 15:53
  • Hi Cindy, thanks for the advice. If I remove these then the syntax of the string breaks... (in the VBA IDE you get "Compile Error: Expected end of statement"). It seems like quite a few other people have reported getting 1004 errors when inserting formulae into cells programatically - I'm wondering if there is some generic "trick" to get this to work? In the meantime, I guess I can chunk up the formula string and try and see which bit of it VBA/Excel doesn't like. – P. S. Williams Sep 07 '18 at 09:50
  • Ok, so I did some testing. See the test sub below. It looks like there something about the way you set the parameters of the VLOOKUP function that is causing the 1004 error. Does anyone know how to use VLOOKUP correctly in VBA? Thanks! – P. S. Williams Sep 07 '18 at 10:24
  • `ActiveWorkbook.Sheets("WerkBonApp").Select ActiveSheet.Range("A18").Select ActiveCell.Formula = "=VLOOKUP(4; WerkBonApp!A10:B14;2;FALSE)"` – P. S. Williams Sep 07 '18 at 10:26
  • This formula works directly in Excel but not when you insert it via VBA - any ideas? `=VLOOKUP(4; WerkBonApp!A10:B14;2;FALSE)"` – P. S. Williams Sep 07 '18 at 11:03
  • A friend found this post on this forum: https://stackoverflow.com/questions/21907106/excel-2010-vba-error-1004-with-formula - which provided the solution - you need to use the `.FormulaLocal` property and it works! Problem solved. – P. S. Williams Sep 10 '18 at 08:51

1 Answers1

0

When trying to insert a complex formula into an Excel cell using VBA, you need to use the .FormulaLocal property.

P. S. Williams
  • 43
  • 1
  • 6