VBA: add value if not in range

[akk]

Is there something I could have as part of a submit command button that would check if a value was in a specified range and if not add it.

So say i had a range A1:A10 which contains a list:

Apples
Potatoes
Tomatos

and I want to add Pears if it isn't already there, if it is there then I don't want anything to happen.

I know how I could add it in the first blank cell, so all I really need to know is how to see if i value exists in a range.

Thanks,

Andrew

 

[BlueTrin]

This returns 0 if Pears is not in A1:A10:

=SUM(IF(A1:A10="Pears",1,0))


EDIT:

This returns TRUE if Pears is in the range, FALSE if not:

=NOT(ISERROR(MATCH("Pears",A1:A10,0)))

 

[akk]

Thank you, thats what I wanted

This returns 0 if Pears is not in A1:A10:

=SUM(IF(A1:A10="Pears",1,0))


EDIT:

This returns TRUE if Pears is in the range, FALSE if not:

=NOT(ISERROR(MATCH("Pears",A1:A10,0)))

 

[MickG]

Hi, Try:-

[COLOR="Navy"]Sub[/COLOR] MG06Oct36
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Dta [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] Found [COLOR="Navy"]As[/COLOR] Boolean
[COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
  Dta = Application.InputBox(prompt:="Please Enter Value ", Title:="Unique Values", Type:=2)
[COLOR="Navy"]If[/COLOR] Dta = "False" [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Dn = Dta [COLOR="Navy"]Then[/COLOR] MsgBox Dta & " - Exists": [COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]
 [COLOR="Navy"]Next[/COLOR] Dn
Range("A" & Rng.Count + 1).Value = Dta
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[akk]

I tried this:

Dim Rng As Range, Dn As Range, Dta As String, Found As Boolean, wsSheet As Worksheet
On Error Resume Next
  Dta = ComboType.Text
If Dta = "" Then Exit Sub
Set wsSheet = Worksheets("Types")
With wsSheet
    Set myRange = .Range("A2", .Range("A65536").End(xlUp))
End With
For Each Dn In Rng
    If Dn = Dta Then Exit Sub
 Next Dn
 With Worksheets("Devices")
        If .Cells(2, 1).Value = "" Then
            lCurrentRow = 2  ' (list is empty - start in row 2)
        Else
            Dim lLastRow As Long
            With Worksheets("Devices")
                lLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
            End With
            lCurrentRow = lLastRow
        End If
    End With
Worksheets("Devices").Cells(lCurrentRow, 3).Value = ComboType.Text

It doesn't seem to work, it exits if it is present but doesn't add if it is.
Any ideas?

Thanks,

Andrew

P.S it seemed to work before I tried to change it

 

[MickG]

Hi, Try This:-

[COLOR="Navy"]Sub[/COLOR] MG06Oct35
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Dta [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] lLastRow [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Dta = ComboType.Text
[COLOR="Navy"]If[/COLOR] Dta = "" [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]
 [COLOR="Navy"]With[/COLOR] Worksheets("Types")
    [COLOR="Navy"]Set[/COLOR] Rng = .Range("A2", .Range("A65536").End(xlUp))
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
            [COLOR="Navy"]If[/COLOR] Dn = Dta [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]
        [COLOR="Navy"]Next[/COLOR] Dn
 [COLOR="Navy"]End[/COLOR] With
 
[COLOR="Navy"]With[/COLOR] Worksheets("Devices")
    lLastRow = IIf(.Cells(2, 1).Value = "", 2, .Cells(.Rows.Count, 1).End(xlUp).row + 1)
    .Cells(lLastRow, 3).Value = ComboType.Text
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[akk]

Thanks, got it working

Andrew

 

[Zack Barresse]

If you're looking for a VBA solution, I recommend using the Find method rather than looping through a range, as it will probably be much more efficient.

 

[Zack Barresse]

If you're looking for a VBA solution, I recommend using the Find method rather than looping through a range, as it will probably be much more efficient.

 

[akk]

Thanks, is there a simple way to modify this so it deletes a value if it finds it?

Andrew