VBA error on form code

Euro5
Euro5 used Ask the Experts™
on
I had been running a form code that enters data into a spreadsheet.
It was working accurately, until I added three txtbox & three labels, now I get an error on line below, which enters a formula into column A on whatever sheet it is on.

Again, this was working properly, so the code is good, just adding the Discount, ED & Min Reduction were added now the formula below is an error.
I will attach the project if this will help.


       .Range("A" & lngNextRow).Formula = "=INDIRECT(""AK""&ROW())&IF(INDIRECT(""B""&ROW())<>"""","" ("","""")&INDIRECT(""B""&ROW())&IF(INDIRECT(""B""&ROW())<>"""",""-"","""")&INDIRECT(""C""&ROW())&IF(INDIRECT(""B""&ROW())<>"""","") "","""")&"" ""&INDIRECT(""E""&ROW())&"" ""&INDIRECT(""AM""&ROW())&"" ""&INDIRECT(""AL""&ROW())"""

Open in new window

What-if-scenario-v4-test-form.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
There's nothing in that line that refers to anything on a form which is where I assume the new textboxes and labels are.

Have you tried entering the formula manually? If you can get it to work manually, converting it to VBA shouldn't be too hard.

Commented:
Indirect does not handle active sheet or active cell

Before your call you can debug using the following
Debug.Print Application.ActiveSheet().ToString()
Debug.Print Application.ActiveCell().ToString()
//If not what you want
 Workbooks("BOOK1 XLS"). Worksheets("Sheet1 ").Activate

On that note create dynamic functions for each step of the way.

Author

Commented:
I know, the line just enters the formula, the only relationship is that it works with the code where the other data is entered into the spreadsheet.
Yes, I have it working manually and I have it working on the old form just fine.
Three steps forward....two steps back.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Can you show the manually entered formula?
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Though I have not downloaded your large file, see if the following line works without an issue..
Range("A" & lngNextRow).Formula = "=INDIRECT(""AK""&ROW())&IF(INDIRECT(""B""&ROW())<>"""","" ("","""")&INDIRECT(""B""&ROW())&IF(INDIRECT(""B""&ROW())<>"""",""-"","""")&INDIRECT(""C""&ROW())&IF(INDIRECT(""B""&ROW())<>"""","") "","""")&"" ""&INDIRECT(""E""&ROW())&"" ""&INDIRECT(""AM""&ROW())&"" ""&INDIRECT(""AL""&ROW())"

Open in new window

Author

Commented:
Manually entered

=INDIRECT("AK"&ROW())&IF(INDIRECT("B"&ROW())<>""," (","")&INDIRECT("B"&ROW())&IF(INDIRECT("B"&ROW())<>"","-","")&INDIRECT("C"&ROW())&IF(INDIRECT("B"&ROW())<>"",") ","")&" "&INDIRECT("E"&ROW())

Open in new window

Author

Commented:
That was it!!!!  I had the .Range  instead of the usual Range
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Replace your VBA formula with the manual one.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial