Euro5
asked on
VBA error on form code
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.
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())"""
What-if-scenario-v4-test-form.xlsm
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().T oString()
//If not what you want
Workbooks("BOOK1 XLS"). Worksheets("Sheet1 ").Activate
On that note create dynamic functions for each step of the way.
Before your call you can debug using the following
Debug.Print Application.ActiveSheet().
Debug.Print Application.ActiveCell().T
//If not what you want
Workbooks("BOOK1 XLS"). Worksheets("Sheet1 ").Activate
On that note create dynamic functions for each step of the way.
ASKER
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.
Yes, I have it working manually and I have it working on the old form just fine.
Three steps forward....two steps back.
Can you show the manually entered formula?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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())
ASKER
That was it!!!! I had the .Range instead of the usual Range
Replace your VBA formula with the manual one.
Have you tried entering the formula manually? If you can get it to work manually, converting it to VBA shouldn't be too hard.