Runtime error "1004"

[rsinha]

Hi,
We have an Excel macro. It works for all other versions but fails with Excel 2013 with following error -

Application defined or Object defined error.

The error occurs at the following line -

Please Login or Register  to view this content.

The rRange is 8:10, 10:20, 12:10, 13:20

Prior to setting the validation it's setting the rRange with following code -

Dim rRange As Range

iLastCol = Range("I6").Value
Set rRange = Range(Cells(iRow, 10).Address + ":" + _
Cells(iRow + 2, iLastCol).Address + "," + _
Cells(iRow + 4, 10).Address + ":" + _
Cells(iRow + 5, iLastCol).Address)

This is a valid range. Also, once error is received I debugged to press F8 a few times. I then checked the validation rule in the actual spreadsheet this macro created and the rule is present in all above cells. Why this error in Excel 2013 only?

[Leith Ross]

Hello rsinha,

Welcome to the Forum!

From what I see there are no coding conflicts. I suggest you post a copy of the workbook for further review.

Please redact any sensitive or confidential information before you post the workbook.

How To Post Your Workbook

1. At the bottom right of the Reply window, Click the button Go Advanced
2. At the top of the Your Message Window, in the first row of icons, Click the Paperclip icon.
3. Click the Add Files button at the top right in the dialog box. This displays the File Manager dialog.
4. Click the Select Files button at the bottom middle of the dialog.
5. In the new window Find the file you want to upload, select it, and Click Open.
6. You will now be back in the File Manager dialog. Click the bottom Middle button Upload File.
7. Wait until the file has completely uploaded before you exit the File Manager dialog.

[TnTinMN]

I have no idea why it only fails in 2013, but is it possible that Validation was already applied to the range?

Try adding .Delete to the beginning of the With block.

Please Login or Register  to view this content.

[rsinha]

Adding .Delete didn't work and the same error occurred on Validation.add

[rsinha]

Hi
I have attached two docs, SCPM-Macro_Tool_v3.3_temp.xls - this is the macro, Input Forecast Download-1record.txt - is the input file on which the macro operates. During debugging session I start F8 at openX macro/OpenMacroTool_Bucketized(). It will ask to browse a file, where I select the attached input file.
At some point the control goes into CopyX macro/CopyInputToModify(bBucketize As Boolean). From there when it reads the 2nd line of the input file it goes to Collab Tools macro/CreateNewCollab().
From there to SetQuantityFields(). From there to DataTools macro/UnlockCells(rRange). This is where adds the validation with following code -


With rRange.Validation
.Add Type:=xlValidateWholeNumber, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlGreaterEqual, _
Formula1:="0"
.ErrorTitle = "Integers"
.ErrorMessage = "Please enter a positive integer value"

and fails at .Add Type:= line.

Thanks for help. Hoping you will continue to This issue is driving me nuts. Thanks again.

[TnTinMN]

Unfortunately, I do not have Excel 2013 to verify your issue.

The only thing that I can think of is the fact that you are using a multi-area range and perhaps something has changed from earlier versions to prevent setting validation on such a range?

You could try the following to see if this hypothesis is valid.

Please Login or Register  to view this content.

[FDibbins]

rsinha, it seems that you also post under the ID "punter"? (same IP 1637.114.103.51)

http://www.excelforum.com/excel-prog...004-issue.html

[rsinha]

FDibbins,
The post - http://www.excelforum.com/excel-prog...004-issue.html, is not mine.

[rsinha]

TnTinMN,
I put in the code that you suggested, i.e -

Please Login or Register  to view this content.

Now when I run it in debug mode I do not get any error at the Validation line. So that's a good progress. But I still get the error when I execute the macro by clicking on the button.
Is there a way I can figure out which code it is executing when clicking on the button?

[TnTinMN]

Now when I run it in debug mode I do not get any error at the Validation line. So that's a good progress. But I still get the error when I execute the macro by clicking on the button.
Is there a way I can figure out which code it is executing when clicking on the button?

Well that is strange. As I stated before, I do not have Excel 2013 so the following is just guess-work on my part.

I know that there were some significant changes in Excel 2013 and I'm starting to wonder if those are coming into play as the code is defining rRange based on the ActiveSheet in SetQuantityFields.

For some reference reading, see: Programming for the Single Document Interface in Excel 2013

Let's try a test to see what Workbook/Sheet rRange is pointing to when it it is defined in SetQuantityFields and you execute the code via clicking the button.

Please Login or Register  to view this content.

If it is not pointing to: "[Input Forecast Download-1record.txt]Modify'!$J$8:$BD$10,$J$12:$BD$13", then we have identified the problem.

[FDibbins]

FDibbins,
The post - http://www.excelforum.com/excel-prog...004-issue.html, is not mine.

ok, but both seem to be from teh same IP

Also, please use code tags when posting code. See my footnote above

[rsinha]

TnTinMN,
I added the line - MsgBox "rRange points to: " & rRange.Address(True, True, xlA1, True). Strangely, the rRange.Address is showing the same value whether run in debug mode or by clicking the button. The value of range in both cases is -

rRange points to: '[IBM Forecast Download-kamran-1record.txt]Modify'!$J$8:$BD$10,$J$12:$BD$13

When clicked on the Button to run and it fails on the Validate line, thereafter I choose to go in debug mode and then it completes the entire code fine.
I do not understand this behavior.

[TnTinMN]

I added the line - MsgBox "rRange points to: " & rRange.Address(True, True, xlA1, True). Strangely, the rRange.Address is showing the same value whether run in debug mode or by clicking the button. The value of range in both cases is -

rRange points to: '[IBM Forecast Download-kamran-1record.txt]Modify'!$J$8:$BD$10,$J$12:$BD$13

When clicked on the Button to run and it fails on the Validate line, thereafter I choose to go in debug mode and then it completes the entire code fine.

Well that ruled out that possibility.

One thing that happens when you enter debug is that control is returned to the OS and the message pump runs.

I really hate to make this next suggestion, but there is nothing to lose at this point. Add a call to DoEvents and see what happens.

Please Login or Register  to view this content.

[rsinha]

When I added Application.DoEvents it gave me following error -

'Object doesn't support this property or method"

So I added just 'DoEvents'. Running this both in debug or via button didn't do anything.
BTW, one difference I see when executing the code in debug mode is that following line of code -
iLastRowIn = Cells.SpecialCells(xlLastCell).Row

gives this error only when running in debug mode -

"You cannot use this command on a protected sheet.
To use this command, you must first unprotect the sheet"

So then I go to the input spreadsheet and there first add Protect Sheet and the Unprotect sheet. Then continue with the program.
This error doesn't occur if I run it by clicking on the button. Not sure if that has anything to do with why the Validate works in debug mode.

[TnTinMN]

When I added Application.DoEvents it gave me following error -

'Object doesn't support this property or method"

So I added just 'DoEvents'. Running this both in debug or via button didn't do anything.

Oops, That should have been VBA.DoEvents

BTW, one difference I see when executing the code in debug mode is that following line of code -
iLastRowIn = Cells.SpecialCells(xlLastCell).Row

gives this error only when running in debug mode -

This leads me back to my previous suspicion about the implicit use of ActiveSheet throughout this code and that perhaps the ActiveSheet (and/or WorkBook) is not what the code is assuming. The code has many Something.Select statements in it and this type of coding in error prone.

It is better to be explicit in code and specify the WorkBook when declaring a Worksheet and to Specify a WorkSheet when declaring a Range. I saw rudiments of full range path specification in parts of the code, but it the style reverted back to using Select.

There is not anything more that I can offer you to solve this problem. Hopefully some one that has Excel 2013 will take a look and be able to offer something.

Good Luck.

[rsinha]

Thank you so much for your help.

Any Excel 2013 gurus out there ??????