VBA error 1004 no cells were found

[SyxP4k]

Good day all!

I am using the below code in a large project. My dependent cells have formulas in them with several resulting in a blank "" cell. I believe Error 1004 no cells were found occurs if a dependent cell is blank. I'm new to VBA, and assuming it would be better to change my code to account for a blank dependent cell rather than ignoring it with Resume Next. I was hoping that testing for not equal to "" (as shown below) would fix the problem but it isn't. Any ideas how I could prevent the error entirely?

If Not Intersect(Target.Dependents, Me.Range("F7:F36")) Is Nothing Then
     If Target.Dependents > 71 And Target.Dependents <> "" Then
           More code here
     End if
End if

Thanks so much!

 

[jproffer]

Can you post your entire code without giving up top secret information?

That would help us to help you.

 

[SyxP4k]

Thanks for looking at my post and offering to help!

It is all top secret information!!! I'll post more if it is helpful. However, it was working without a hitch until adding the below code into the worksheet change event:

If Not Intersect(Target.Dependents, Me.Range("F7"F36)) Is Nothing Then
     If Target.Dependents > 71 And Target.Dependents <> "" Then
          Select Case True
               Case LCase(shtInput.Range("C" & Target.Row).Value)="pw"
                    shtInput.Range("I" & Target.Row).Value="Y"
               Case Else
                    shtInput.Range("I" & Target.Row).Value=""
          End Select
     Else
          shtInput.Range("I" & Target.Row).Value=""
     End If
End If

F7:F36 is an IF statement resulting in either a blank "" or a sum of that row (ie. sum(D7:E7)) It works correctly if I have a value in F of the target row, but I get the run time error if there is a blank.

 

[jproffer]

Try this instead:

If Not Intersect(Target.Dependents, Me.Range("F7:F36")) Is Nothing Then
     If Target.Dependents > 71 And Target.Dependents <> "" Then
          Select Case True
               Case LCase(shtInput.Range("C" & Target.Row).Value) = "pw"
                    shtInput.Range("I" & Target.Row).Value = "Y"
               Case Else
                    shtInput.Range("I" & Target.Row).Value = ""
          End Select
     Else
          shtInput.Range("I" & Target.Row).Value = ""
     End If
End If

You closed the quotes on your range too early. Should have been ("F7:F36")

 

[SyxP4k]

Good catch! Someone else is using my file so I couldn't copy/paste the code into the post. I guess my typing/proofreading skills need work.

Shouldn't the

If Target.Dependents > 71 And Target.Dependents <> "" Then

test for a value greater than 71 and not blank? Now that I type that question it seems redundant. Of course greater than 71 isn't blank! So...If everything runs properly when there is a value in the target cells, I'll try and test for a blank first. If it works I'll post back what I came up with.

Thanks for the help!

 

[jproffer]

Yea I was thinking that. No need to check for both, but it's not hurting anything either.