Vba find & replace a number by a macro.Excel

Question

I'm making an heuristic analyse and i have the following problem:

I want to find in column D numbers that match with column J and replace them by a "0". You can see what I'm trying to do on this image: http://imageshack.us/f/96/heuristic.jpg/

Some parts of the code:

   Dim i,j As Integer
   Dim temp As String
   Dim x As Integer
   Dim d As String

   i = Application.CountA(Range("E:E")) + 10
   'number of cell with values
   j = Application.CountA(Range("J:J")) + 10
   For j = 11 To j
        temp = Range("J" & j).Value
          For i = 11 To i
           d = Range("D" & i).Value
           *
           Next
  Next

At the * lies the problem, I can't figure out how to pass over the comma "," in column D, and store the data. I want to compare the temp with value on "d", but "d". Can I have multiple numbers on the same cell, like "3,2,1", and if there is any match like temp = 3, then d= "0,2,1".

English is not my native language so I hope you can understand what I want.

Answer 1

There's no need for VBA. Standard Excel functions will work:

1. Make a new column somewhere near column D.
2. In your new column, use this formula =IF(ISERROR(VLOOKUP(D11,J:J,1,FALSE)),D11,0). This formula looks up the value of D11 in column J. If a match is found, 0 is returned. Otherwise, D11 is returned.
3. If you want, you can hide column D to avoid confusion.

If you have to do this multiple times, you may want to have an unformatted 'raw data' tab and a clean 'display tab'. Does that help?