VBA Clear Cells for one range if cell in other range is blank

[DoodlesMama]

Hi Everyone,

Currently using Office 2010, Windows 7

I'm looking for code that will clear only the cells in named range [TOT_TIME1] if the cells (same row) in named range [CAT1] are empty.

I've tried the code below, but keep getting a "Type Mismatch" error.
If [CAT1].Value = "" Then
[TOT_TIME1].ClearContents
End If

 

[joemeshuggah]

Maybe this?

Sub Test()
x = [Category1].Rows.Count
For i = 1 To x
If [Category1].Cells(i, 1).Value = "" Then
[TotTime1].Cells(i, 1).ClearContents
End If
Next i
End Sub

 

[Rick Rothstein]

If the row range for both named ranges is the same, then this should work...

Sub MatchBlanksInTOT_TIME1toBlanksInCAT()
  On Error Resume Next
  Intersect(Range("CAT1").SpecialCells(xlBlanks).EntireRow, Range("TOT_TIME1")).Clear
  On Error GoTo 0
End Sub

 

[Rick Rothstein]

If the row range for both named ranges is the same, then this should work...

Sub MatchBlanksInTOT_TIME1toBlanksInCAT()
  On Error Resume Next
  Intersect(Range("CAT1").SpecialCells(xlBlanks).EntireRow, Range("TOT_TIME1")).Clear
  On Error GoTo 0
End Sub

Another, probably better, way (a one-liner, by the way)...

Sub MatchBlanksInTOT_TIME1toBlanksInCAT()
  Range("TOT_TIME1") = Evaluate("IF(LEN(" & Range("CAT1").Address & ")," & Range("TOT_TIME1").Address & ","""")")
End Sub