Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Run Time Error 1004
|
|
|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Run Time Error 1004
Resolved · Urgent Priority · Version 2003
Reply Mon 7 Sep 2009, 10:16Delegate Gareth said...
Gareth has attended:
Excel VBA Intro Intermediate course
Excel Intermediate course
Excel Advanced course
Run Time Error 1004
Hey
I have inherited this spreadsheet with the following macro.
Sub VerifyNumeric()
Dim cellInRange As Excel.Range
Set cellInRange = Apapplication.Intersect(Range(ActiveCell.Address), Range("NumericRange"))
If Not (cellInRange Is Nothing) Then
If Not IsNumeric(ActiveCell.Value) Then
MsgBox "You can only enter numbers in this cell!"
ActiveCell.ClearContents
End If
End If
End Sub
Sub auto_open()
ThisWorkbook.Sheets("Custody").OnEntry = "VerifyNumeric"
End Sub
Sub EnforceNumericalValidation()
ThisWorkbook.Sheets("Custody").OnEntry = "VerifyNumeric"
End Sub
There's no comments been left by the programmer so not 100% sure what this macro does.
Anyway, the problem users are getting is that when they go to fill out a free text cell with an update (numeric or alpha) the following error message is showing.
'Run Time Error' '1004'
Method 'Range' of object ' _ Global' failed.
I have another spreadsheet with the same macro and the the error doesn't appear there. I have tried de bugging it with little luck. Any suggestions?
Thanks in advance
Gareth
For upcoming training course dates see: Pricing & availability
RE: Run Time Error 1004
Hi gareth
Thank you for your question.
I have detected the following error in the third line of your code
Set cellInRange = Apapplication.Intersect(Range(ActiveCell.Address), Range("NumericRange"))
The word application is misspelled. This would probably lead to the error you are getting.
If this doesn't solve the problem, please reply and I will investigate further
Regards
Stephen
Reply Mon 7 Sep 2009, 13:45Delegate Gareth said...
RE: Run Time Error 1004
Hi Stephen thanks for you reply. The spelling mistake was obvious and something I should of picked up on! However I have amended the spelling and I'm still getting the same error.
When I de bug the macro that line is highlighted in bright yellow so the problem does lie there I believe.
Thanks for your help.
RE: Run Time Error 1004
Hi Gareth
Apologies for the delay in responding. I have been away from the office for a few weeks delivering on site training.
I have copied the code into a workbook and it works fine. The run time error you describe might arise because the code refers to a named range "NumericRange", and this needs to be present in the active workbook.
Is it possible that the named range exists in the workbook were the code works, but is absent elsewhere?
Regards
Stephen
Thu 24 Sep 2009: Automatically marked as resolved.
|