View Full Version : insert formula - Run-time error '1004' - Application defined or object-defined error
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
09-26-2017, 03:23 AM
Fix the typos in your formula.
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
09-26-2017, 04:21 AM
Semicolon and Dail ydose
Paul_Hossler
09-26-2017, 05:13 AM
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)
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
09-26-2017, 05:45 AM
Sheet1.Range("E5").FormulaArray = Formula1
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
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
09-26-2017, 06:01 AM
Can you post a sample workbook?
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
09-26-2017, 10:14 AM
Post an example using Go Advanced / Manage Attachments
Hi
I attached xlsm sample file.
Select "Parentetal Only" from drop-down list in cell E4.
Thanks in advance.
Regards
zk69
mdmackillop
09-26-2017, 04:17 PM
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
09-26-2017, 05:04 PM
Try it with no data validation on E5
20484
I had to change the formulas to use commas
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
Hi mdmackillop
Thank you so much.
...FormulaArray works.
I appreciate your help.
So many thanks.
Regards
zk69
Paul_Hossler
09-27-2017, 09:44 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.