Solved excel macro: how to find word one sheet to another

June 3, 2015 at 06:19:24
Specs: Windows 7
Hi,

I have a same situation in the sheet1 contain the list of Block name (total 341), and in the second sheet the addresses (total over 10000), is it possible to mark red each cell in block sheet that occurs in address sheet. but the block name should be exactly match with the word in the address.

for example block name: "Chanditala"
in address : 116,Chanditala,Kolkata Pin:700053

need to highlight RED if block sheet if it is present in the address(exact)


See More: excel macro: how to find word one sheet to another

Report •


✔ Best Answer
June 4, 2015 at 09:38:09
Like I said, a simple macro does the trick:

Sub RedBlock()
'Loop through Sheet1 A2:A341
 For Each b_cell In Sheets(1).Range("A2:A341")
'Search Sheet2 A2:A1000 for Block
  With Sheets(2).Range("A2:A10000")
   Set b = .Find(b_cell, lookat:=xlPart)
'If Block is found, color Sheet1 cell Red
    If Not b Is Nothing Then
     Sheets(1).Range(b_cell.Address).Interior.ColorIndex = 3
    End If
   End With
  Next
End Sub

The only issue would be if the address cell contains words like "Chanditalao" or "kChanditala". Since those words contain "Chanditala", they would be considered a match. There is an easy fix for that, but I didn't include it just to keep the code simple.

If that fix is required, replace this instruction:

Set b = .Find(b_cell, lookat:=xlPart)

with this:

Set b = .Find("," & b_cell & ",", lookat:=xlPart)

That instruction ensures that the search string (the Block) contains a leading and trailing comma, just like in your address string example.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03



#1
June 3, 2015 at 06:39:01
I have done a very quick example, the code works but it can be refined to run quicker and perhaps remove the need for the TestSame procedure

This will look at the values in Sheet 1 column 1
and compare them to the string in Sheet 2 column 1

if it finds a match then the value in sheet 2 will be colored red

Private Sub CommandButton1_Click()
Dim uRange
Dim lRange
Dim Bcell As Range
Dim TestVal As String

Sheet2.Cells.ClearFormats

Set uRange = Range("A1")
Set lRange = Range("A4")

For Each Bcell In Range(uRange, lRange)

    TestVal = Bcell.Value
    TestSame (TestVal)

Next Bcell

End Sub

Sub TestSame(TestString As String)

Dim fRange
Dim bRange
Dim nCell As Range

Set fRange = Sheet2.Range("A1")
Set bRange = Sheet2.Range("A10")

For Each nCell In Sheet2.Range(fRange, bRange)
           
    If InStr(1, nCell.Value, TestString, vbTextCompare) Then
        nCell.Interior.ColorIndex = 3
    End If
Next nCell

End Sub

Hope this helps


Report •

#2
June 3, 2015 at 07:05:42
i have tried but it is not working...may be some problem in calling the funtions

Report •

#3
June 3, 2015 at 07:30:23
Iv noted an issue with the code but dont have time right now to debug it, will try again when i get a chance.

EDIT... iv checked the code and it works fine for me. can you be more specific when you say it doesnt work. what happens when you run the code? do you get any errors? how are you running the code?

message edited by AlwaysWillingToLearn


Report •

Related Solutions

#4
June 3, 2015 at 12:16:40
You do not need a Macro to do what you want.
Conditional Formatting and Defined Names
will also work.

It may seem a bit complicated, but follow along and it will work.

list of Block name (total 341)
First we Define a name for your Block names on Sheet 1

Select your list of names, IE A1:A341
On the Ribbon,
Select Formula
Select Define Name
In the pop up window,
in the Name box enter your name, IE: BlockList
In the Scope box make sure it says Workbook
In the Referes To box make sure it reads: =Sheet1!$A$1:$A$341
Click OK

Next we enter a Define Name to tidy up your address strings.
On the Ribbon,
Select Formula
Select Define Name
In the pop up window,
in the Name box enter your name, IE: TidyUp
In the Scope box make sure it says Workbook
In the Referes To box you will enter the formula:

=SUBSTITUTE(SUBSTITUTE(Sheet2!$A1,".",""),",","")

This will remove any periods or commas.
Click OK

Next, on Sheet 2, your Address sheet,
is where we do the Conditional Formatting


1) Select your range of cells, IE A1:A10000
2) On the ribbon click Home, Select Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.
5) Enter the formula:

=SUMPRODUCT(--ISNUMBER(FIND(" "&TRIM(BlockList)&" "," "&TidyUp&" ")))

6) Click on the Format button
7) Select the Fill Tab
8) Select a Red color
9) Click OK
10) Click OK

All your Address, that contain one of your Blocked names
should turn red.

See how that works for you.

And in the future, Excel questions are best asked in the Office Software Forum.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#5
June 3, 2015 at 19:04:09
It would help if you told us where in the sheets your values are located. For example, are your Block names in Column A? Is that example address string in a single cell? What column(s) are your addresses in?

With that information in hand, a simple macro using .Find should make quick work of your task. No need to loop through 10000 cells looking for matching values.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
June 4, 2015 at 06:49:25
Sheet1 contains block name:A2:A341 -- example for "Chanditala"
Sheet2 contains Addressees:A2:A10000 -- example for -116,Chanditala,Kolkata Pin:700053

I want to make "Chanditala" RED in sheet1, if it occurs at least once in sheet2, should be matched exactly.

i have tries all the above but that makes sheet2 words red not sheet1 unique block name.


Report •

#7
June 4, 2015 at 07:19:03
OOP's got it backwards, try this:

Same idea, but a different formula

the addresses (total over 10000)
First we Define a name for your Address list on Sheet 2

Select your list of Addresses, IE A1:A10000
On the Ribbon,
Select Formula
Select Define Name
In the pop up window,
in the Name box enter your name, IE: AddressList
In the Scope box make sure it says Workbook
In the Referes To box make sure it reads: =Sheet2!$A$1:$A$10000
Click OK

Next, on Sheet 1, your Block sheet,
is where we do the Conditional Formatting

1) Select your range of cells, IE A1:A341
2) On the ribbon click Home, Select Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.
5) Enter the formula:

=COUNT(IF(SEARCH(A1,AddressList),1,""))

6) Click on the Format button
7) Select the Fill Tab
8) Select a Red color
9) Click OK
10) Click OK

All your Block words, that are contained in any
of your Address's should turn red.

MIKE

http://www.skeptic.com/


Report •

#8
June 4, 2015 at 07:28:05
not worked yet.. may be bcos address contain other parts like pin code, state, commas and other

Report •

#9
June 4, 2015 at 07:31:13
Are you talking about my Conditional Formatting solution or the Macro?

Could you be a bit more specific then just "not worked yet",
does anything happen?
Do you get an error message?

I tested it on the Address and Block sample you posted and it worked OK.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#10
June 4, 2015 at 07:35:37
talking about conditional formatting..just tried again it making red..also that not in the address list...will send you data..give me your email..will check that

Report •

#11
June 4, 2015 at 08:34:10
As a guess, I would say that it is probably finding words within words.

If you have the blocked word: Hill
and an address of: Haverhill
then it will give you a hit.

will send you data
Please read this How To which explains the use of the < PRE > tags
in posting data to this site.

http://www.computing.net/howtos/sho...

I would prefer to keep all the data in one place on the forum,
so that others, who have a similar need can read and learn from
what we do here.

Please use Column Letters and Row Numbers when you post the data.


MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#12
June 4, 2015 at 09:38:09
✔ Best Answer
Like I said, a simple macro does the trick:

Sub RedBlock()
'Loop through Sheet1 A2:A341
 For Each b_cell In Sheets(1).Range("A2:A341")
'Search Sheet2 A2:A1000 for Block
  With Sheets(2).Range("A2:A10000")
   Set b = .Find(b_cell, lookat:=xlPart)
'If Block is found, color Sheet1 cell Red
    If Not b Is Nothing Then
     Sheets(1).Range(b_cell.Address).Interior.ColorIndex = 3
    End If
   End With
  Next
End Sub

The only issue would be if the address cell contains words like "Chanditalao" or "kChanditala". Since those words contain "Chanditala", they would be considered a match. There is an easy fix for that, but I didn't include it just to keep the code simple.

If that fix is required, replace this instruction:

Set b = .Find(b_cell, lookat:=xlPart)

with this:

Set b = .Find("," & b_cell & ",", lookat:=xlPart)

That instruction ensures that the search string (the Block) contains a leading and trailing comma, just like in your address string example.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#13
June 4, 2015 at 12:31:03
Try this modified formula:

=COUNT(IF(SEARCH(","&A1&",",AddressList),1,""))

This ensures that the search string contains a leading
and trailing comma, like in your address string example.

MIKE

http://www.skeptic.com/


Report •

#14
June 5, 2015 at 11:12:03
Its great..working fine DerbyDad03....also the condition formatting...but i prefer a micro...i have just added some code to it and it is perfect now....
Set b = .Find("," & b_cell & ",", lookat:=xlPart)
Set b = .Find(b_cell & ",", lookat:=xlPart)
Set b = .Find("," & b_cell & , lookat:=xlPart)
Set b = .Find("b_cell , lookat:=xlPart)

It is working great.....thanks all


Report •

#15
June 5, 2015 at 11:41:34
I'm glad you got it working so I'll assume you have simply made some typos in your latest post.

These 2 instructions will not work:

Set b = .Find("," & b_cell & , lookat:=xlPart)
                           ^
                       Extra Ampersand

Set b = .Find("b_cell , lookat:=xlPart)
              ^
        Extra Single Quote

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#16
June 5, 2015 at 13:11:49
Yes..its typo errors..but i run the code perfectly thanks

Report •

#17
June 6, 2015 at 00:45:20
Can we make this interactive, having dialog box asking for range in sheet1 and then sheet2

Report •

#18
June 6, 2015 at 01:48:56
I don't have access to Excel right now, but you should be able to use an InputBox to get the range from the user. The third example at this site does just that:

http://www.ozgrid.com/VBA/inputbox.htm

If you figure it out on your own, please let us know so that we don't waste time working on an issue that you've already solved.

I have also reset the Best Answer choice for this thread since it appears that we are not done.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •


Ask Question