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=Daily dose!$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 (http://www.vbaexpress.com/forum/member.php?87-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=Daily dose!$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=Daily Dose!$B$2:$B$58),0),3)

[zk69]

Hi Hi mdmackillop (http://www.vbaexpress.com/forum/member.php?87-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 errorRegards
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 (http://www.vbaexpress.com/forum/member.php?9803-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=Daily dose!$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=Daily dose!$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=Daily dose!$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
20470

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

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

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

20484

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