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
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
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
