Code Run-time error '1004'

[pjhageman]

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

 

[Dave Peterson]

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]

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]

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