Compare cell value to items in string

[PamG]

I need to compare cell values in a column to a comma-delimited string. The
column has ID numbers and the string consists of a comma-delimited string of
ID numbers.

Any help would be appreciated....

Thanks
Pam

 

[Chip Pearson]

Pam,

You've left out a lot of important details, but the following might
get you going in the right direction.

Sub AAA()
Dim R As Range
Dim V As Variant
Dim S As String
S = "11,22,33" '<<<< This is the data to test against
V = Split(S, ",")
For Each R In Range("A1:A10") '<<<< This is the range with data
' put alert in next column right
If IsError(Application.Match(CStr(R.Value), V, 0)) = True Then
R(1, 2) = "no match"
Else
R(1, 2) = "match"
End If
Next R
End Sub

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
The San Diego Project Group, LLC
(email is on the web site)
USA Central Daylight Time (-5:00 GMT)

 

[PamG]

Thanks, Chip! Now I have another problem....this is my code that should copy
a row of data in one worksheet to the first blank row in another worksheet.
The problem is that the second worksheet has an additional column as column
one, so I want to copy worksheet 1 row (which starts on column A) to be
copied to worksheet 2 column B.

Sub check()

Dim stroldstring As String

' create string of "Issues" IDs
Windows("FIS1.18.xls").Activate
Sheets("Issues").Select
Range("B2", Range("B2").End(xlDown)).Select

stroldstring = ""
For Each cell In Selection
stroldstring = stroldstring & "," & Replace(cell.Value, " ", "")
Next

Dim R As Range
Dim V As Variant

V = Split(stroldstring, ",")
Sheets("Sheet1").Select
For Each R In Range("A1", Range("A1").End(xlDown))

If IsError(Application.Match(CStr(R.Value), V, 0)) = True Then
R.EntireRow.Copy Sheets("Issues").Range("B65536").End(xlUp)
.EntireRow.PasteSpecial Paste:=xlPasteValues


Else

End If
Next R

End Sub

This code still copies to worksheet 2, column A!