Looking for help in Excel

Closed
Manoj - Oct 29, 2010 at 05:17 AM
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Oct 29, 2010 at 10:21 PM
Hello,

I am looking for help in excel, A1-A10 has few names and B1-B20 has some names as well, is it possible that A1-A10 looks into B1-B20 and if it finds the name then the value should come 0 or else the name from B column.

Thank you

2 replies

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Oct 29, 2010 at 05:38 AM
not clear. I am rephrasing if the values in a1:a10 is avilable in B1:B20 that b value is 0 other wise no change.
if this is what you want run this macro test

BEFORE DOING THIS SAVE YOUR ORIGINAL FILES SAFELY SOMEWHERE SO THAT IT CAN BE RETREIVED IF THERE IS A MESS UP.

the macro is


Sub test()
Dim r As Range, c As Range, cfind As Range, x
Set r = Range("A1:a10")
For Each c In r
x = c.Value
Set cfind = Range("B1:B20").Cells.Find(what:=x, lookat:=xlWhole)
If Not cfind Is Nothing Then
cfind = 0
End If
Next c
End Sub


if this is not what you require post data sheet and how it should look after running a macro.
0
I am looking for the following:


A B C
Pankaj Pankaj 0
Kamal Shweta 0 as shweta is there in A5
Madhu Madhu 0
Vinay Prashant Prashant
Shweta Rahul Rahul
shashi shashi
Navin Navin
Kamal 0

Thank for the previous reply, however i dont know how to use it the way u said.
0
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Oct 29, 2010 at 10:21 PM
This is the result. what is the original data.
0