Check if cell value exists in named range

[melewie]

Hi All,

I have been handed some work but am having some trouble with it, I need to do some data validation on a cell (A FRIGGIN MERGED CELL!) the user needs to complete a form (not a userform, created on a sheet with many merged cells) the problem I'm having is I need to check the value of cell C7 (C7:E7) against a range called line_RNG before the user can submit the form. The way I would normally go is

Dim LineTest As Range

On Error Resume Next
    Set LineTest = Range("Line_RNG").Find(What:=range("c7".value), LookAt:=xlWhole)
On Error GoTo 0


If LineTest Is Nothing Then
    MsgBox "you're a mug do it properly"
    GoTo lastline
End If

However this doesn't seem to work and LineTest always remains 'nothing', either i'm missing something or could this be due to the cells being merged?? it does pick up the value of the cell but linetest remains 'nothing' any help is hugely appreciated

P.S I cannot change the layout of the form/I don't want to create a huge amount of work.

Thanks

Lew

 

[steve the fish]

Try this:

Dim c As Range
Dim x As String

x = Range("C7").Value
 
With Range("Line_RNG")
    Set c = .Find(x, LookIn:=xlValues, LookAt:=xlWhole)
    If Not c Is Nothing Then
        MsgBox "Found You"
    Else
        MsgBox "Not Here"
    End If
End With

 

[JackDanIce]

I'd first check if Range("Line_RNG") contains the full expected value of the merged cell and I'm not sure if using What:=Range("C7").Value is infact the entire value of the C7:E7 merged cell contents

One untested suggestion to try is:

Dim LineTest as String
Dim mCell     as String

LineTest = Range("Line_RNG").Value 'you could try changing .Value to .Text
mCell = Range("C7").Value ' or .Text

If Instr(LineTest, mcell) > 0 Then
  Msgbox LineTest & " contains " & mcell
Else
  Msgbox LineTest & " does not contain " & mcell
End If

I'm not sure this will work but worth a try.

 

[Fishboy]

Hi All,

I have been handed some work but am having some trouble with it, I need to do some data validation on a cell (A FRIGGIN MERGED CELL!) the user needs to complete a form (not a userform, created on a sheet with many merged cells) the problem I'm having is I need to check the value of cell C7 (C7:E7) against a range called line_RNG before the user can submit the form. The way I would normally go is

Dim LineTest As Range

On Error Resume Next
    Set LineTest = Range("Line_RNG").Find(What:=range("c7".value), LookAt:=xlWhole)
On Error GoTo 0


If LineTest Is Nothing Then
    MsgBox "you're a mug do it properly"
    GoTo lastline
End If

However this doesn't seem to work and LineTest always remains 'nothing', either i'm missing something or could this be due to the cells being merged?? it does pick up the value of the cell but linetest remains 'nothing' any help is hugely appreciated

P.S I cannot change the layout of the form/I don't want to create a huge amount of work.

Thanks

Lew

If Application.WorksheetFunction.CountIf(Range("line_RNG"), Range("C7").Value) > 0 Then
MsgBox "you're a mug do it properly"
End If

 

[melewie]

Try this:

Dim c As Range
Dim x As String

x = Range("C7").Value
 
With Range("Line_RNG")
    Set c = .Find(x, LookIn:=xlValues, LookAt:=xlWhole)
    If Not c Is Nothing Then
        MsgBox "Found You"
    Else
        MsgBox "Not Here"
    End If
End With

Cheers Steve got this to work with a tiny change, and thank you to everyone else who took the time to reply (sure your versions would have worked just as well ) but I don't want to spend any more time on this.. so thank you all and have a grand day!

Dim c As Range
Dim x As String


Set Rng = Worksheets("Validation Lists").Range("Line_RNG")


x = Range("C7").Value
 
With Rng
    Set c = .Find(x, LookIn:=xlValues, LookAt:=xlWhole)
    If Not c Is Nothing Then
        MsgBox "Found You"
    Else
        MsgBox "Not Here"
    End If
End With

 

[27560]

If Application.WorksheetFunction.CountIf(Range("line_RNG"), Range("C7").Value) > 0 Then
MsgBox "you're a mug do it properly"
End If

This worked perfectly for me, thanks!