Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Run-time error 1004 - Your Code...

0

Excel VBA Course - Beginner to Expert

This line of your code gives me a Run-time error...

It is found in many of the worksheets like Assignment 3

Worksheet Section Assignment - Completed Version

And I have attached that file.

[EDITED] As mentioned in a comment, you need to use the private contact form on TeachExcel for support for the courses - you cannot post the files publicly.

excel
MS Office Version: 2019 Professional Plus
posted by: Marcco on Mon Mar 20, 2023 at 2:46 am
Answer
Discuss

Discussion

Hi Marcco and welcome to the Forum. 

Questions on the VBA course should be directed to Don directly (using the Contact link above). Files from that course (and other paid ones) should NOT be uploaded to the Forum- please edit your original question to remove the file.

I'll try to answer you later, once you've done that. If I can't you'll need to use the Contact route (but Don is a busy man so it may take a while for an answer ) 
John_Ru (rep: 6142) Mar 20, '23 at 5:01 am
I am sorry about that, won't happen again.
I reread the rules and I don't see where I missed this?
It metions a sample file which is what I uploaded not my code... Not arguing, just confused..?
Also
I went to remove that file, but it appears to have been removed already.
Did I miss it ?? I can be pretty dense sometimes lol...
Else we're good?
Thanks Much,

Marc
Marcco Mar 20, '23 at 8:29 pm
Marc. Thanks for trying to remove the file (but the EDIT by Don already did that and advised you about questiobs on the course).

Don't recall if or where users were told to pose questions away from the Forum but Don put a lot of effort in creating the course and can't recover that cost if files "leak" on to the internet.

Sorry I can't help you this time but hope you enjoy the rest if the course. 
John_Ru (rep: 6142) Mar 21, '23 at 6:58 am
If you need further assitance, please use the private contact form at the top of the page on TeachExcel - from there I can give you direct personal support.
don (rep: 1989) Mar 22, '23 at 8:04 am
Add to Discussion

Answers

0
Selected Answer

The use of .Formula2R1C1 (vs .FormulaR1C1) is what causes the Runtime error.
Formula2 can only be used in "Dynamic Array Aware" Excel versions. 

This feature had been broadly released on Jan 2020 to Office 365 subscribers. So 2016 and 2019 are NOT "Dynamic Array Aware" versions.

posted by: Marcco on Wed Mar 22, 2023 at 6:36 am
Discuss

Discussion

Okay Marc but that's what I told you in my revised answer earlier! I also asked Don to correct the course as he sees fit. 
John_Ru (rep: 6142) Mar 22, '23 at 7:36 am
Marco, Formula vs Formula2 is mentioned in the tutorial "Working with Formulas" in the section "Get & Input Data into the Worksheet". The small file for that tutorial talks about it in the comments at the bottom of the module and provides a link from Microsoft for further reading on the topic. Perhaps I should have put the comment higher, but it is in the course notes.
don (rep: 1989) Mar 22, '23 at 8:03 am
Add to Discussion
0

Marcco

I don't get any error when I press the Add Item button on the Input worksheet of the file you attached (which needs to be removed please- see my Discussion point below your question). It correctly adds the Qty* Price formula to cel F14, F15 etc. as items are added.

You have the named ranges defined and sheet names correct so I can't see a cause for such an error.

I can force worksheet #VALUE! errors by putting text in the Qty and Price fields (or using a comma as decimal separator where mine is the fullstop/ period). No error 1004 though.

Revision 22 March 2022:

The failed line is:

Worksheets("Invoice").Range("subtotalRow").Offset(-2, 1).Formula2R1C1 = "=RC[-1]*RC[-2]"

That works in later versions of Excel (365, 2021, Excel for the web) which support Dynamic Arrays (which make very easy to enter an array formula e.g.) but not earlier versions..

Given you use Excel 2019 Pro+, remove the 2, so:

Worksheets("Invoice").Range("subtotalRow").Offset(-2, 1).FormulaR1C1 = "=RC[-1]*RC[-2]"

Hope this fixes things for you. If so, please remember to mark this answer as Selected.

posted by: John_Ru (rep: 6142) on Mon Mar 20, 2023 at 6:09 am
Discuss

Discussion

Hello,
Sorry about the file, I totally missed that memo.
I open the file and on the input page I enter text a qty and an amt then I click the "Add Item" button and I get the error box. The entries I made are not cleared and over on the "Invoice" tab the entries appear on a new line, but the formula is not there to calculate the line total.
NOTE: This is NOT my code it is the code in the sloution file that came with the course.
In my solution I commented it out and copy the entire row and it works like a charm...
Marcco Mar 20, '23 at 8:25 pm
Marc. If VBA hit that error, it would not do the lines after (which apply the formula etc.). I couldn't replicate your error but it sounds like you sorted it out. 
John_Ru (rep: 6142) Mar 21, '23 at 7:01 am
It doesn't do the lines after. It stops at the point of applying the formula and fails there. The lines above that line of code copy the values entered in the input.
However it appears that it is the use of .Formula2R1C1 (vs .FormulaR1C1) is what is causing the error.
You can use Formula2 only on Dynamic Array aware Excel versions.  My guess is that you are using Office 365, Since this feature had been broadly released on Jan 2020 to Office 365 subscribers.

You might pass that forward...
Marc
Marcco Mar 22, '23 at 4:41 am
@Marc I'm using 365- see my revised Answer above.

@Don- I think this problem might trip up other users. Will you please check and revise the ccourse if necessary?
John_Ru (rep: 6142) Mar 22, '23 at 6:19 am
Looking over it! Thanks John!
don (rep: 1989) Mar 22, '23 at 7:56 am
Marco, Formula vs Formula2 is mentioned in the tutorial "Working with Formulas" in the section "Get & Input Data into the Worksheet". The small file for that tutorial talks about it in the comments at the bottom of the module and provides a link from Microsoft for further reading on the topic. Perhaps I should have put the comment higher, but it is in the course notes.
don (rep: 1989) Mar 22, '23 at 8:02 am
@Don Thanks for that detail. 
John_Ru (rep: 6142) Mar 22, '23 at 8:09 am
@John_Ru Sorry John I completely missed your revised answer. I missed Don's comments too... Lol
Thank You Both ..!

Marc
Marcco Mar 23, '23 at 7:57 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login