How can i stop this running when formula is ""

[tonywatsonhelp]

Hi Everyone,
I have a bit of code that works great
I have two row merged toghther and they have a formula in it that returns "" on error,
so I don't want the macro to run if the target value is ""

please help

thanks

Tony

heres my code

Private Sub Selected(ByVal Target As Range)

Application.ScreenUpdating = False
Dim Lastrow As Long
''On Error GoTo Bye1

If Not Intersect(Target, Range("AD109:AD140")) Is Nothing Then
Cancel = True
If Target.Cells.Count <> 2 Then Exit Sub
ans = Target.Offset(0, -4).Value
Application.ScreenUpdating = False
Sheets("Details").Select
Sheets("Control").Range("D2").Value = ans
Call ADD_Value
Call Get_Actions_List
Bye1:

End If
End Sub

 

[Jeffrey Mahoney]

First, the SUB you posted doesn't look like a standard Event SUB. Are you calling this sub and sending a range to it? A standard SUB for getting an event when a user selects a different cell is:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

What it looks like is that the "Target" range could potentially be a range of cells (more than one merged or unmerged cells). If that is so, then testing if the target has a value of "" is ambiguous. You could get an error. I can see that you are testing for the merged cells by:

If Target.Cells.Count <> 2 Then Exit Sub

Is this the only merged cell in the range? The number of cells in a selected range could also show a count of unmerged cells.

You should test if the specific Range is in the target before testing if it is blank or not:

If [COLOR=#333333]Not Intersect(Target, Range("AD120")) Is Nothing and Range("AD120").value = "" then exit sub
[/COLOR]

Hope that helps

Jeff

 

[tonywatsonhelp]

Hi Jeff,
thanks for your help,
once you pointed out that "

then testing if the target has a value of "" is ambiguous. You could get an error." I just made a feeder row that has all the values in a single cell and did a check using offset and it works perfectly.
thanks for your help
Tony
​