Index match and isnull

[SeanWise]

So I've been trying without much success to integrate isnull into my Index/Match vba code.

Basically, the program needs to find a Date in column C, this date was presaved in a variable called YRow.
Then it needs to check whether a corresponding row in Column E is blank.
After that, it'll take action as necessary.

I've been playing with this, but no dice:

If IsEmpty(Application.Evaluate("=INDEX(ActiveSheet.Range("E:E") ,MATCH("YRow, Activesheet.Range("C:C"),0))").Value) = True Then
     MsgBox "It's Empty"  <---this is just for debugging
End If

Any ideas?

 

[Tetra201]

Try this:

If IsEmpty(Application.Evaluate("INDEX(" & ActiveSheet.Range("E:E").Address & ",MATCH(YRow," & ActiveSheet.Range("C:C").Address & ",0))")) Then ...

 

[SeanWise]

Thank you , but unfortunately, that code now just skips the msgBox. I need the code to return 0 or 1 or true or similar if column E is blank. If it is blank, It needs to run other code.

 

[Tetra201]

Thank you , but unfortunately, that code now just skips the msgBox. I need the code to return 0 or 1 or true or similar if column E is blank. If it is blank, It needs to run other code.

I just made your IsEmpty(Application.Evaluate(...)) construct working; the rest is up to you.

If it skips the MsgBox, then either YRow is not found in column C or the corresponding cell in column E is not empty.
Being "empty-looking" or "blank-looking" does not necessarily mean being empty.

 

[SeanWise]

Ok, so I changed it to this:

var1 = Application.Evaluate("INDEX(" & ActiveSheet.Range("E9:E1000").Address & ",MATCH(YRow," & ActiveSheet.Range("C9:C1000").Address & ",0))")
If var1 = 0 Then

but I keep getting a "run time error 13: type mismatch ". I have changed Var1 to long, string, double, integer and even variant, but get the same error.
Been doing this all day. I cannot find the solution no matter how hard I try. Another hint please?

 

[Tetra201]

Ok, so I changed it to this:

var1 = Application.Evaluate("INDEX(" & ActiveSheet.Range("E9:E1000").Address & ",MATCH(YRow," & ActiveSheet.Range("C9:C1000").Address & ",0))")
If var1 = 0 Then

but I keep getting a "run time error 13: type mismatch ". I have changed Var1 to long, string, double, integer and even variant, but get the same error.
Been doing this all day. I cannot find the solution no matter how hard I try. Another hint please?

Hint: this happens because YRow is not found in ActiveSheet.Range("C9:C1000").