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

[CWay]

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

[Siddharth Rout]

Search the forum on .Find

[westconn1]

you can also use worksheet formulas to do this without vba

[Ecniv]

westconn1 means vlookup - check excels help files

[westconn1]

westconn1 means vlookup - check excels help files

or if sumif > 0

actually i meant countif > 0

[Siddharth Rout]

The thread title says VBA...

[westconn1]

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

[AngelAtwOrk]

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