Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Run-time error '1004'
In the below code, I am receiving a Run-time error ‘1004’:
Application-defined or object-defined error. The line If Empty(.Range…. is highlighted yellow. Could someone suggest a fix? Thanks, Phil Private Function CheckRange(Cell As Range) Dim sMsg As String With Worksheets("Scorecard") If IsEmpty(.Range(Cell)) Or .Range(Cell).Value <= 0 Then If .Range(Cell).MergeArea.Address(False, False) < Cell Then sMsg = "Weight for Cell(s) " & _ ..Range(Cell).MergeArea.Address & _ " must be entered, and must be greater than zero." Else sMsg = "Weight for cell " & _ ..Range(Cell).Address & _ " is required" End If CheckRange = sMsg & sMsg & vbCrLf End If End With End Function --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Run-time error '1004'
Cell is already a range, so you don't put it inside Range().
If IsEmpty(.Range(Cell)) Or .Range(Cell).Value <= 0 Then should be more like: If IsEmpty(Cell) Or Cell.Value <= 0 Then And a few more to clean up, too. "pjhageman <" wrote: In the below code, I am receiving a Run-time error ‘1004’: Application-defined or object-defined error. The line If Empty(.Range…. is highlighted yellow. Could someone suggest a fix? Thanks, Phil Private Function CheckRange(Cell As Range) Dim sMsg As String With Worksheets("Scorecard") If IsEmpty(.Range(Cell)) Or .Range(Cell).Value <= 0 Then If .Range(Cell).MergeArea.Address(False, False) < Cell Then sMsg = "Weight for Cell(s) " & _ Range(Cell).MergeArea.Address & _ " must be entered, and must be greater than zero." Else sMsg = "Weight for cell " & _ Range(Cell).Address & _ " is required" End If CheckRange = sMsg & sMsg & vbCrLf End If End With End Function --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Run-time error '1004'
Dave, You mentioned more code to clean up?? I was given this code, an
am not a code writer, so if you could help make more efficient, coul you suggest... Dave Peterson wrote: *Cell is already a range, so you don't put it inside Range(). If IsEmpty(.Range(Cell)) Or .Range(Cell).Value <= 0 Then should be more like: If IsEmpty(Cell) Or Cell.Value <= 0 Then And a few more to clean up, too. "pjhageman <" wrote: In the below code, I am receiving a Run-time error ‘1004’: Application-defined or object-defined error. The line If Empty(.Range…. is highlighted yellow. Could someone suggest a fix? Thanks, Phil Private Function CheckRange(Cell As Range) Dim sMsg As String With Worksheets("Scorecard") If IsEmpty(.Range(Cell)) Or .Range(Cell).Value <= 0 Then If .Range(Cell).MergeArea.Address(False, False) < Cell Then sMsg = "Weight for Cell(s) " & _ Range(Cell).MergeArea.Address & _ " must be entered, and must be greater than zero." Else sMsg = "Weight for cell " & _ Range(Cell).Address & _ " is required" End If CheckRange = sMsg & sMsg & vbCrLf End If End With End Function --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Run-time error '1004'
I meant that you had additional range(cell)'s that should be changed to just
cell. I made some assumptions about your function and did it this way: Option Explicit Private Function CheckRange(Cell As Range) Dim sMsg As String If IsEmpty(Cell) Or Cell.Value <= 0 Then If Cell.MergeArea.Address(False, False) _ < Cell.Address(False, False) Then sMsg = "Weight for Cell(s) " & _ Cell.MergeArea.Address & _ " must be entered, and must be greater than zero." Else sMsg = "Weight for cell " & _ Cell.Address & _ " is required" End If CheckRange = sMsg & vbCrLf End If End Function If you're passing a range, it comes with all the things that a range has--including it's own worksheet. So using the "with worksheets("scorecard")" doesn't help. The passed range has its own worksheet. It's up to you to call the function with enough information: Sub testme() MsgBox CheckRange(Worksheets("sheet1").Range("a1")) End Sub This line got changed, too: If .Range(Cell).MergeArea.Address(False, False) < Cell Then You're comparing the .address(false,false) (which evaluates to something like A1) to the value in the cell. I'm guessing you wanted to see if the cell was in a mergedarea. You culd use: If Cell.MergeArea.Address(False, False) < Cell.Address(False, False) Then or just this: If Cell.MergeArea.Cells.Count 1 Then But even better, VBA is forgiving enough to allow you to use the .mergearea with a cell that isn't merged. And if the warning message could be made the same--whether or not the cell is merged with others: Option Explicit Private Function CheckRange(Cell As Range) Dim sMsg As String If IsEmpty(Cell) Or Cell.Value <= 0 Then sMsg = "Weight for Cell(s) " & _ Cell.MergeArea.Address(False, False) & _ " must be entered, and must be greater than zero." End If CheckRange = sMsg & vbCrLf End Function And some versions of xl are picky about "cell.value <= 0" if the value is text. And I think that this is mostly a matter of style, but sometimes when I'm validating a field/value, I'll use a boolean value and check each requirement. If it fails, I'll set that boolean value to false. I find I can add more checks pretty easily--but it could be written differently with the same outcome. Option Explicit Private Function CheckRange(Cell As Range) Dim sMsg As String Dim cellOk As Boolean cellOk = False If IsEmpty(Cell) Then cellOk = False ElseIf IsNumeric(Cell.Value) = False Then cellOk = False Else If Cell.Value <= 0 Then cellOk = False End If End If End If If cellOk Then 'do nothing Else sMsg = "Weight for Cell(s) " & _ Cell.MergeArea.Address(False, False) & _ " must be entered, and must be greater than zero." End If CheckRange = sMsg & vbCrLf End Function "pjhageman <" wrote: Dave, You mentioned more code to clean up?? I was given this code, and am not a code writer, so if you could help make more efficient, could you suggest... Dave Peterson wrote: *Cell is already a range, so you don't put it inside Range(). If IsEmpty(.Range(Cell)) Or .Range(Cell).Value <= 0 Then should be more like: If IsEmpty(Cell) Or Cell.Value <= 0 Then And a few more to clean up, too. "pjhageman <" wrote: In the below code, I am receiving a Run-time error ‘1004’: Application-defined or object-defined error. The line If Empty(.Range…. is highlighted yellow. Could someone suggest a fix? Thanks, Phil Private Function CheckRange(Cell As Range) Dim sMsg As String With Worksheets("Scorecard") If IsEmpty(.Range(Cell)) Or .Range(Cell).Value <= 0 Then If .Range(Cell).MergeArea.Address(False, False) < Cell Then sMsg = "Weight for Cell(s) " & _ Range(Cell).MergeArea.Address & _ " must be entered, and must be greater than zero." Else sMsg = "Weight for cell " & _ Range(Cell).Address & _ " is required" End If CheckRange = sMsg & sMsg & vbCrLf End If End With End Function --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson * --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
runtime error code 1004 | Excel Worksheet Functions | |||
Error Code 1004 when exporting from VB app to Excel 2007 | Excel Discussion (Misc queries) | |||
Run Time Error '1004' | Excel Worksheet Functions | |||
Run time error 1004, General ODBC error | New Users to Excel | |||
VBA code to save gives error 1004??? | Excel Programming |