[RESOLVED] [VBA] See if a value exists in a Range

I know this is a common situation but I cannot seem to put this code together. I am trying to take the value of a cell, see if it matches the value in a cell in another group of cells on the same worksheet, and if so update A THIRD cell with a Y/N result, 'exists' in range or 'does not exist' in range.


Thank you for your help.
CWay

Search the forum on .Find

you can also use worksheet formulas to do this without vba

westconn1 means vlookup - check excels help files

Quote:

---

westconn1 means vlookup - check excels help files

---

or if sumif > 0

actually i meant countif > 0

The thread title says VBA...

i read that and offered an alternative, if it was suitable

I am not good at VBA so I'm sure that my coding is clumsy but it works "touch wood". I use both vba .find from a listbox to match data and the excel vlookup to get related data if match is found. Then I copy and paste the part where I use the excelfunction so it won't be formulas in the sheet. This is what my code snippet looks like if it is of any help, the sub "SaknasVärde" that I call in the first sub could be translated as "MissingValue" and the sub "KlistraIn" copies and paste.

Worksheets("Tidrapport").Activate
Range("A4").Select
Set C = Cells.Find(ComboBox1.Value, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not C Is Nothing Then
C.Offset(Range("$A$3").Value, 0).Activate
Else

Selection.End(xlToRight).Select
Selection.Offset(0, 1).Select
Selection.Value = ComboBox1.Value
Selection.Offset(-1, 0).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(R[1]C,Lista,3,FALSE)"
SaknasVärde
KlistraIn
Sub Saknasvärde()
If Application.WorksheetFunction.IsNA(ActiveCell.Value) Then
ActiveCell.Value = ""
End If

Well, someone else will surely give you a better code:)