Case insensitive

[Jesslynn]

Can anybody help me?
Example: I want the word "the"

it must be able to copy any word with "the" inside.
Like, These, tHEsE, ThE, thEresa would be copied.

The code looks like this..

Sub NonSensitive()




Dim rCell As Range
For iCntr = 1 To 30




    For Each rCell In Range("I1:I10")
   
    
If Cells.Find(what:=Sheets("Sheet1").Cells(iCntr, 9).Value, after:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlColumns, SearchDirection:=xlNext, MatchCase:= _
False).Activate Then






    If Cells(iCntr, 9) = Cells(5, 11) Then
        


 Cells(iCntr, 10) = Cells(iCntr, 9)
 
 
 End If
        
End If
'MsgBox rCell.Address & " has " & rCell & " in it"






    Next rCell
    Next iCntr


  
End Sub

The code above only copied the "The" word.
Thanks in advance!

 

[Special-K99]

Your method should be

Change what you're looking at to Upper case (temporarily, ie search UPPER(cell) ) and search for "THE" not "the" or "tHE"

 

[hiker95]

Jesslynn,

Try adding one line of code like the following, above the line that begins with Sub NonSensitive() to your macro:

Option Compare Text
Sub NonSensitive()

 

[Jesslynn]

the option compare helped in the caps and non caps! Thank you so much

But the words containing "the" is not copied. Can anybody tell me how?

 

[hiker95]

Jesslynn,

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?

To start off, and, so that we can get it right on the first try:

Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post a small screen shot (NOT a graphic, or, picture, or, PNG file, or, flat text) try one of the following:

MrExcel HTML Maker
If you do not know how to install and how to use HTML Mr.Excel Maker
https://www.youtube.com/watch?v=JycvgGppxt0&feature=youtu.be

Excel Jeanie
Download

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

To test the above:
Test Here


The following is a free site:

Or, you can upload your workbook to (the BLUE link-->) Box Net ,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[Jesslynn]

This is what I'm having with the code above:

Bean
Soy	Soy
Soya
SoyaBean
Soya Bean
sorry

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>

But this is what is want:

Bean
Soy	Soy
Soya	Soya
SoyaBean	SoyaBean
Soya Bean	Soya Bean
sorry

<colgroup><col><col></colgroup><tbody>
</tbody>

Any word that consist of soy will be copied into the next column.

 

[hiker95]

Jesslynn,

Here is a macro solution for you to consider.

You can change the raw data worksheet name in the macro.

Sample raw data in column A, and, the results in column B:

Excel 2007
	A	B
1	Bean
2	Soy	Soy
3	Soya	Soya
4	SoyaBean	SoyaBean
5	Soya Bean	Soya Bean
6	sorry
7
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Compare Text
Sub FindSoy()
' hiker95, 07/28/2015, ME871409
Dim c As Range
Application.ScreenUpdating = False
With Sheets("Sheet1")   '<-- you can change the sheet name here
  For Each c In .Range("A1", .Range("A" & .Rows.Count).End(xlUp))
    If InStr(c, "soy") Then c.Offset(, 1).Value = c.Value
  Next c
  .Columns(2).AutoFit
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the FindSoy macro.

 

[Rick Rothstein]

Using hiker95's setup in Message #7, here is another macro you can also try...

Sub Soy() Dim Addr As String Addr = "A1:A" & Cells(Rows.Count, "A").End(xlUp).Row Range(Addr).Offset(, 1) = Evaluate(Replace("IF(ISNUMBER(SEARCH(""soy"",@)),@,"""")", "@", Addr)) End Sub

 

[Rick Rothstein]

Using hiker95's setup in Message #7, here is another macro you can also try...

Sub Soy() Dim Addr As String Addr = "A1:A" & Cells(Rows.Count, "A").End(xlUp).Row Range(Addr).Offset(, 1) = Evaluate(Replace("IF(ISNUMBER(SEARCH(""soy"",@)),@,"""")", "@", Addr)) End Sub

Here is the above code generalize so that you can more easily use it for any word, not just "soy" (change the red highlighted word to the word you want to find)...

Sub Soy() Dim Addr As String, WordToFind As String WordToFind = "[B][COLOR="#FF0000"]soy[/COLOR][/B]" Addr = "A1:A" & Cells(Rows.Count, "A").End(xlUp).Row Range(Addr).Offset(, 1) = Evaluate(Replace("IF(ISNUMBER(SEARCH(""" & WordToFind & """,@)),@,"""")", "@", Addr)) End Sub

 

[Jesslynn]

Thank you so much both of you! Both code works perfectly!!