Check if in column A some words are present

[assurdo]

Hi,
how do I check, with a macro, if a .txt file that I have imported in Excel, contains some word? If it contains them, I need to show a error message, if it does not contain it, it's fine.
When the file txt is imported in Excel, it is in the worksheet "Import". So I can check in the column A of "Import" is the words are found. Thank you.

 

[nardagus]

I don't know of the amount of the data you need to import.
I came up with a formula. First some info.

I created two sheets:
- Import - this one contains imported data (obviously )
- Verification - this one contains keywords to search for and a searching formula


Excel 2016 (Windows) 32 bit

	A
Imported data
Imp1
Imp2
car
forest
something
smthelse
anything
home
sky

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

</tbody>

Import sheet above​

Excel 2016 (Windows) 32 bit

	A	B	C
keyword to look for	Keywords found?	Amount of keywords found
car	Keywords found!
forest

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Verification sheet​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]{=IF(SUM(IF(ISERROR(MATCH(A2:A4,Import!$A$2:$A$13,0)),0,1))>0,"Keywords found!","")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]{=SUM(IF(ISERROR(MATCH(A2:A4,Import!$A$2:$A$13,0)),0,1))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

What do you think?

 

[assurdo]

I don't know of the amount of the data you need to import.
I came up with a formula. First some info.

I created two sheets:
- Import - this one contains imported data (obviously )
- Verification - this one contains keywords to search for and a searching formula


Excel 2016 (Windows) 32 bit

	A
Imported data
Imp1
Imp2
car
forest
something
smthelse
anything
home
sky

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

</tbody>

Import sheet above​

Excel 2016 (Windows) 32 bit

	A	B	C
keyword to look for	Keywords found?	Amount of keywords found
car	Keywords found!
forest

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Verification sheet​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B2[/TH]
[TD="align: left"]{=IF(SUM(IF(ISERROR(MATCH(A2:A4,Import!$A$2:$A$13,0)),0,1))>0,"Keywords found!","")}[/TD]
[/TR]
[TR]
[TH]C2[/TH]
[TD="align: left"]{=SUM(IF(ISERROR(MATCH(A2:A4,Import!$A$2:$A$13,0)),0,1))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

What do you think?

It's fine, thank you!
Another solution can be:

1) Formula in cell B10 for example: =CONTA.SE(Import!A:A; "Word to search")
+
2) Macro: If Range("B10") > 0 Then MsgBox "....."

 

[nardagus]

Ooooh. You want to look for one word only?

That does change things a bit.

Your macro is almost ready. Do you still need assistance with it?

One more thing. My previous formula can be wrote differently:

Excel 2016 (Windows) 32 bit

	B
Keywords found!

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]3[/TD]

</tbody>

Verification​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B3[/TH]
[TD="align: left"]{=IF(SUM(IF(COUNTIF(Import!$A$2:$A$13,Verification!A2:A4)>0,1,0))>0,"Keywords found!","")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]