0

I realize there are already a number of great responses for dealing with VBA TypeMismatch errors in Excel, but they all seem quite case-specific and I'm admittedly too much of a VBA n00b to follow them.

So, I wondered if you might be able to help me with this specific predicament: I have made up a workbook and I want rows to automatically hide themselves if the value in column C is less than today's date. *I assigned the following Macro to an ActiveX Command Button:* 

Sub Hide_PastOrders()
 Dim MyRange As Range, C As Range
 Application.ScreenUpdating = False
 Application.Calculation = xlCalculationManual
 Set MyRange = Range("d1:d1000")
 MyRange.EntireRow.Hidden = False
 For Each C In MyRange
     If IsDate(C.Value) And C.Value < Date Then
         C.EntireRow.Hidden = True
     End If
 Next
 Application.ScreenUpdating = True
 Application.Calculation = xlCalculationAutomatic
 End Sub

It was working fine until recently, but now sometimes results in a 'Type MisMatch' and I'm not sure why. It only seems to be an issue on a couple of the worksheets and they are all assigned the same macro.

I have also implemented an ActiveX command button to 'Show all rows': 

Sub ShowAll_Click()
ActiveSheet.Cells.EntireRow.Hidden = False
End Sub

Haven't had any issues with this one.

Also, I'm not sure that this is relevant, but I have created a number of internal references in my document. i.e. typed "=A5" in B5 for example so that if there are changes to our rentals for multiple pieces of equipment, I would only have to type the information in once. This proves to be very frustrating because everytime I encounter a 'TypeMismatch' Error, it reverts certain cells to "=REF".

Please let me know if I'm taking the wrong approach or if you have any suggestions!

Thanks, Alexandra.

Community
  • 1
  • 1
user2668306
  • 9
  • 1

1 Answers1

3

The expression in the if statement is not short-circuited in VBA. Try this:

If IsDate(C.Value) Then
     If  C.Value < Date Then
         C.EntireRow.Hidden = True
     End If
End If

See http://en.m.wikipedia.org/wiki/Short-circuit_evaluation

Tarik
  • 10,810
  • 2
  • 26
  • 40
  • Worked like a charm! Thanks for the speedy response! – user2668306 Aug 09 '13 at 20:31
  • I really don't understand this site sometimes, more so the people on it. You try and help someone and people criticise you for it, without even saying why. Anyway I up-voted man. Your answer is fine in my opinion. – Reafidy Aug 09 '13 at 23:11
  • @Reafidy Not that I have put a lot of effort on this one but look at this http://stackoverflow.com/questions/18046003/questions-about-binary-search-tree/18046597#18046597 and zero. Amazingly, I got 50 points for a little nothing: http://stackoverflow.com/questions/18092056/net-resolves-a-class-to-the-wrong-dll/18092152#18092152 I wonder what keeps me going... – Tarik Aug 10 '13 at 02:03
  • +1. Keep going Tarik, there will always be those who appreciate and those who do not! – Ioannis Aug 10 '13 at 02:32
  • Yeah I understand your frustration, take a look at www.ozgrid.com/forum. I'm an admin there. Some really good people there, you would be welcome. – Reafidy Aug 10 '13 at 02:39