Type Mismatch

[Ed Raymond]

Hello,

New to VBA, why are the following lines a type mismatch?

Set Impact = Worksheets("Sheet2").Range("C4:C38")
If Impact = Empty Then
MsgBox "No Calculation Possible", vbExclamation

When I run debug, the MsgBox line gives me the error.

 

[Norie]

Ed

Are you sure the line causing the error is the 3rd one?

And are you sure you've posted all the code?

What you've posted so far won't even compile because it's missing an End If.

If that's added then the error occurs on the If statement not the message box part.

 

[Ed Raymond]

Norrie,

Thank you. Here is evrything.

Private Sub GardCalc_Click()
'Error Handling ... no impact data
Dim Impact As Range
Set Impact = Worksheets("Sheet2").Range("C4:C38")
If Impact = Empty Then
MsgBox "No Calculation Possible", vbExclamation
Else: MsgBox "Ready to Rock"
End If
End Sub

 

[Norie]

Ed

You can't use Empty like that.

What are you actually trying to do?

 

[Jonmo1]

I think you're trying to see if there is any data in the range right? And if no data in the range, then...msgbox..

instead of
If Impact = Empty Then

try

If Worksheetfunction.CountA(Impact) = 0 Then

Hope this helps.

 

[Ed Raymond]

Ed

You can't use Empty like that.

What are you actually trying to do?

I am trying to make sure the user has entered "values". The spreadsheet operator enters an X if an event is a failure and a O if it is a pass. The spreadhseet counts the number of X events and using a formula, calculates the point where 50 percent pass and 50 percent fail. I am trying to automate the calculation so the operator simple enters the "event" and the spreadsheet does the rest.

 

[Ed Raymond]

I think you're trying to see if there is any data in the range right? And if no data in the range, then...msgbox..

instead of
If Impact = Empty Then

try

If Worksheetfunction.CountA(Impact) = 0 Then

Hope this helps.

Jon,

That works, what does CountA do?

 

[Jonmo1]

Glad you got it working...

That works, what does CountA do?

counts NON BLANK Cells

 

[Ed Raymond]

Glad you got it working...



counts NON BLANK Cells

Of course, sorry for the dumb question

 

[doofusboy]

If LEN(your_cell) = 0

also a good way to see if cell has contents.