convert a single-celll range to a cell

**jimredfield** · 12-28-2012, 03:27 PM

The code below generates a type mismatch error as shown. I think it's because 'Target' is a single-cell range, not a cell proper, so 'Target.Value' fails but I don't know how to convert the range to a cell. Help appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)
	If Intersect(Target, Range("InputRange")) Is Nothing Then
		Exit Sub  'the cell that changed isn't in the range we're concerned with
	ElseIf Target.Value = "" Then                                                                         '<------ type mismatch error here
		Target.Value = Target.Offset(0, 3).Value    're-assert the default value
	End If
End Sub

Thanks - Jim

Moderators Note: Please follow Forum Rule #3 and use code tags. Added this time, but please use them in the future…Thanks.

**OllieB** · 12-28-2012, 03:43 PM

Hi Jim,

The usage of Target.Value is correct in this context. Perhaps the compare against "" (which implies it is a string) is causing the problem?

You could try


ElseIf LenB(Target.Value) = 0 Then

i.e. LenB is the fastest way to check if a variable is empty

**AB33** · 12-28-2012, 03:45 PM

The Target.Value is looking for the Range("InputRange")) . What is in it? It looks like a named range. The code will trigger if the target and named range intersect

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("InputRange")) Is Nothing Then
       'the cell that changed isn't in the range we're concerned with
       If Target.Value = "" Then
       'Do something
         ElseIf Target.Value = "" Then                                                                         '<------ type mismatch error here
        Target.Value = Target.Offset(0, 3).Value    're-assert the default value
        End If
            End If
End Sub

**Norie** · 12-28-2012, 03:58 PM

The If statement will only work if Target is one cell and doesn't have an error.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("InputRange")) Is Nothing Or Target.Cells.Count > 1 Or IsError(Target.Value) Then
        Exit Sub  'the cell that changed isn't in the range we're concerned with
    ElseIf Target.Value = "" Then
        Application.EnableEvents = False    '<------ type mismatch error here
        Target.Value = Target.Offset(0, 3).Value    're-assert the default value
        Application.EnableEvents = True
    End If
End Sub

**jimredfield** · 12-28-2012, 06:43 PM

Thanks all for the quick help. Olaf, I couldn't your solution to work (continued to get mismatch errors); AB33, I didn't try your solution because Norie's solution (which I saw at the same time) was more desirable with respect to error catching and since it worked perfectly I never looked back. Again, thanks all. - Jim (Moderator, sorry about the lack of code tags.)