[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

VB script to search through Excel

Posted on 2011-04-24
10
Medium Priority
?
369 Views
Last Modified: 2012-05-11
I have an excel workbook that has 2 worksheets, WRK1 and WRK2 , on one worksheet WRK2 I have one column that has names and I would like to search those names if they Exist in WRK1 and if they are found they will be written on the WRK2 next column beside the existing name , if not found then the word "Not Found" will be written next to searched name in the next column.

This script will help me avoid using CTRL +F and search through thousands of names one at a time if they exist in both worksheets,

Thanks
0
Comment
Question by:jskfan
  • 4
  • 4
9 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35456360
jskfan, Here is a small tutorial that I wrote sometime back. This is for VBA but can be amended if you want to run it from vbscript. Vbscript and VBA Excel code are slightly different.

http://www.vbforums.com/showthread.php?p=3922547

Hope it helps.

Sid
0
 

Author Comment

by:jskfan
ID: 35456505
it looks good.
Could you please just paste here the script that can do the task I am looking for ?
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35456569
jskfan: Could you please upload a sample of your workbooks. Maybe we might even be able to do it using Countif() or Vlookup() formulas.

Sid
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:jskfan
ID: 35456632
I have it at work..
here is the example.

I have one Workbook for instance WRKBK1 that has 2 Worksheets WRK1 and WRK2

WRK1 has a column for instance named NAME,
Name
ABCD
XYZF
TRYP
MITH
etc..

WRK2 has also a column named Name:
BCDG
FRHG
TRYP
MITH

now the script will read from  WRK2 names, since BCDG is not in WRK1 it will put in WRK2 in the column nest to the colum NAME, the word " Not Found"
Since TRYP and MITH are found in WRK1, it will write in WRK2 in the column next to the column Name, the word " Found"


0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 1600 total points
ID: 35456715
This simple formula will do.
Assuming that the data is in Col A in WRK1 and WRK2 then put this formula in Cell B2 of WRK2

=IF(COUNTIF(WRK1!A:A,WRK2!A1)>0,"Found","Not Found")

and simply drag it down. No code/script is required :)

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35456718
>>>Cell B2 of WRK2

Sorry, I mean cell B1 of Wrk2.

Sid
0
 

Author Comment

by:jskfan
ID: 35456905
I will try it later thanks
0
 
LVL 46

Assisted Solution

by:aikimark
aikimark earned 400 total points
ID: 35459471
if you have 'thousands' of rows, you will probably get much better performance using a dictionary object, as shown in the snippet.

Option Explicit

Public Sub FindOrNot()
  Dim rngThing As Range
  Dim dicNames As Object
  Set dicNames = CreateObject("Scripting.Dictionary")
  Application.ScreenUpdating = False
  For Each rngThing In Worksheets("WRK1").Range("A1", Worksheets("WRK1").Range("A1").End(xlDown))
    dicNames.Add rngThing.Value, 1
  Next
  For Each rngThing In Worksheets("WRK2").Range("A1", Worksheets("WRK2").Range("A1").End(xlDown))
    If dicNames.exists(rngThing.Value) Then
      rngThing.Offset(0, 1).Value = rngThing
    Else
      rngThing.Offset(0, 1).Value = "Not Found"
    End If
  Next
  Application.ScreenUpdating = True
  dicNames.RemoveAll
  Set dicNames = Nothing
End Sub

Open in new window

0
 

Author Closing Comment

by:jskfan
ID: 35472820
Excellent
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question