insert formula - Run-time error '1004' - Application defined or object-defined error

[zk69]

Hi

I use a Microsoft Excel 2013, I'm trying to dynamically set a formula into a cell using VBA code.

(i) I declared Const (Formula1).

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.DisplayStatusBar = False
Dim Product As String
Const Formula1 = "=INDEX(Dailydose!$A$2:$C$58;MATCH(1,($E$3=Dailydose!$A$2:$A$58)*($D$5=Dail ydose!$B$2:$B$58),0),3)"
....


(ii) I applied Sheet.Range.FormulaLocal command, but I got an error message: Run-time error '1004' - Application defined or object-defined error
If Target.Address = "$E$4" Then
....
Select Case Range("E4")
Case "Parenteral Only":
DoUnLockCell ("E5")
DoSetYellowColorCell ("E5")
Sheet1.Range("E5").FormulaLocal = Formula1
DoLockCell ("E5")
.....


(iii)
I tried with "Sheet.Range.FormulaArray", but I run into the same error message.

If I use Formula1 (=index(...)...) on the worksheet directly, I need to use special characters around the formula. { and }


I would appreciate if somebody could help me.


Regards
zk69

[mdmackillop]

Fix the typos in your formula.

[zk69]

Hi mdmackillop

Thanks for your quick answer.

The formula looks like this:
Const Formula1 = "=INDEX(Dailydose!$A$2:$C$58;MATCH(1,($E$3=Dailydose!$A$2:$A$58)*($D$5=Dail ydose!$B$2:$B$58),0),3)"

what is wrong with this?

Regards
zk69

[mdmackillop]

Semicolon and Dail ydose

[Paul_Hossler]

Const Formula1 = "=INDEX(Dailydose!$A$2:$C$58;MATCH(1,($E$3=Dailydose!$A$2:$A$58)*($D$5=Dail  ydose!$B$2:$B$58),0),3)"/

Comma, not semicolon and no space

=INDEX(DailyDose!$A$2:$C$58,MATCH(1,($E$3=DailyDose!$A$2:$A$58)*($D$5=DailyDose!$B$2:$B$58),0),3)

[zk69]

Hi Hi mdmackillop

"Dail ydose" was a misspellingin my mail.

I have to use semicolon ( in formula. Then it works, the formula is copied into the cell, but the result value is N/A.
If I click on cell, I see formula in it without opening and closing braces { }.
If I enter CTRL-SHIFT-ENTER in cell, the formula gives back correct value, but how can i do it (entering CTRL-SHIFT-ENTER) with using VBA code.

If I use "Sheet.Range.FormulaArray", I run into the same error message.
Run-time error '1004' - Application defined or object-defined error

Regards
zk69

[mdmackillop]

Sheet1.Range("E5").FormulaArray = Formula1

[zk69]

Sorry.
Of course I use this command: Sheet1.Range("E5").FormulaArray = Formula1

Running VBA code stops in this line and I got an error message: Run-time error '1004' - unable to set the formulaarray property of the range class

[zk69]

Hi Paul_Hossler

Thanks for your help.
I have to use semicolon based on my regional setting.
There is no space in formulas.

Regards
zk69

[mdmackillop]

Can you post a sample workbook?

[zk69]

Hi

Here is VBA code.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.DisplayStatusBar = False
Dim Product As String
Const Formula1 = "=INDEX(Dailydose!$A$2:$C$58;MATCH(1;($E$3=Dailydose!$A$2:$A$58)*($D$5=Dailydose!$B$2:$B$58);0);3)"
Const Formula2 = "=INDEX(Dailydose!$A$2:$C$58;MATCH(1;($E$3=Dailydose!$A$2:$A$58)*($D$6=Dailydose!$B$2:$B$58);0);3)"
Const Formula3 = "=INDEX(Dailydose!$A$2:$C$58;MATCH(1;($E$3=Dailydose!$A$2:$A$58)*($D$7=Dailydose!$B$2:$B$58);0);3)"
    If Target.Address = "$E$4" Then
       Product = Range("E3").Value
       If Product = "" Then
                DoUnLockCell ("A38")
                Range("A38").Value = "...................................."
                DoLockCell ("A38")
       Else
                DoUnLockCell ("A38")
                Range("A38").Value = "...................................."
                DoLockCell ("A38")
       End If
       DoResetCell ("E5:E7")
       Select Case Range("E4")
            Case "Parenteral Only":
                DoUnLockCell ("E5")
                DoSetYellowColorCell ("E5")
                Sheet1.Range("E5").FormulaArray = Formula1
                DoLockCell ("E5")
                DoUnLockCell ("A39")
                Range("A39").Value = ""
                DoLockCell ("A39")                
            Case "Oral Only":
                DoUnLockCell ("E6")
                DoSetYellowColorCell ("E6")
                Sheet1.Range("E6").FormulaArray = Formula2
                DoLockCell ("E6")
                DoUnLockCell ("A39")
                Range("A39").Value = ""
                DoLockCell ("A39")                
            Case "Inhalation Only":
                DoUnLockCell ("E7")
                DoSetYellowColorCell ("E7")
                Sheet1.Range("E7").FormulaArray = Formula3
                DoLockCell ("E7")
                DoUnLockCell ("A39")
                Range("A39").Value = ""
                DoLockCell ("A39")
            Case "Parenteral and Inhalation":
                DoUnLockCell ("E5")
                DoSetYellowColorCell ("E5")
                Sheet1.Range("E5").FormulaArray = Formula1
                DoLockCell ("E5")
                DoUnLockCell ("E7")
                DoSetYellowColorCell ("E7")
                Sheet1.Range("E7").FormulaArray = Formula3
                'DoLockCell ("E7")
                DoUnLockCell ("A39")
                Range("A39").Value = "...................................."
                DoLockCell ("A39")
            Case ""
                DoResetCell ("E5:E7")
                DoUnLockCell ("A39")
                Range("A39").Value = ""
                DoLockCell ("A39")
        End Select
    End If
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.DisplayStatusBar = True
End Sub

If i run vba code, i got this error message ---> 1. jpg
1.jpg

I debug code: FormulaArray ="" ----> 2. jpg
2.jpg

I debug code: Formula1 = ""=INDEX(Dailydose!$A$2:$C$58;MATCH(1;($E$3=Dailydose!$A$2:$A$58)*($D$5=Dai lydose!$B$2:$B$58);0);3)"" ----> 3. jpg
3.jpg

But in cell E5 is empty!

Regards
zk69

[mdmackillop]

Post an example using Go Advanced / Manage Attachments

[zk69]

Hi

I attached xlsm sample file.

Select "Parentetal Only" from drop-down list in cell E4.

Thanks in advance.

Regards
zk69

[mdmackillop]

Const Formula1 = "INDEX(Dailydose!$A$2:$C$58,MATCH(1,--($E$3=Dailydose!$A$2:$A$58)*--($D$5=Dailydose!$B$2:$B$58),0),3)"

[Paul_Hossler]

Try it with no data validation on E5

Capture.JPG

I had to change the formulas to use commas

[zk69]

Hi Paul

Thank you so much.
It works.

I don't understand why comma need to be used, because some days ago - in other workbook - the semicolon separator worked.
I removed data validation from cells as well.

I appreciate your and mdmackillop's help.

You are great at Excel VBA programming.

Regards
zk69

[zk69]

Hi mdmackillop

Thank you so much.
...FormulaArray works.

I appreciate your help.

So many thanks.

Regards
zk69

[Paul_Hossler]

Hi Paul

Thank you so much.
It works.

I don't understand why comma need to be used, because some days ago - in other workbook - the semicolon separator worked.
I removed data validation from cells as well.

I appreciate your and mdmackillop's help.

You are great at Excel VBA programming.

Regards
zk69

1. Yes, mac's very good - I've learned a lot also

2. I used commas because of my regional settings

3. The Data Validation seems to be the real reason that .FormulaArray for E5 wasn't working