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!

 

[Jesslynn]

Hi, another small question.
May I know what code i could use to paste the results to the next sheet?

Sheets(Sheet1).Range(1,1).Value = Sheets(Sheet2).Range(1,1)

I used the above code and it copied the entire column A words from sheet 1 into column A in sheet 2.

 

[hiker95]

Jesslynn,

Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.

 

[hiker95]

Jesslynn,

Here is another macro solution for you to consider, based on Rick Rothstein's code, that will ask for the word to find, using an InputBox.

Sub FindWord()
' hiker95, 07/29/2015, ME871409
Dim Addr As String, Word As String
Word = InputBox("Type in the word that you want to find.")
Addr = "A1:A" & Cells(Rows.Count, "A").End(xlUp).Row
Range(Addr).Offset(, 1) = Evaluate(Replace("IF(ISNUMBER(SEARCH(""" & Word & """,@)),@,"""")", "@", Addr))
End Sub

 

[hiker95]

Hi, another small question.
May I know what code i could use to paste the results to the next sheet?

Sheets(Sheet1).Range(1,1).Value = Sheets(Sheet2).Range(1,1)

I used the above code and it copied the entire column A words from sheet 1 into column A in sheet 2.

Jesslynn,

Sorry, I missed that request.

So that we can get it right on the first try:

1. Can we have a screenshot of the raw data in Sheet1?

2. And, can we have a screenshot of the results in Sheet2?

 

[Jesslynn]

These is what I have

Sheet 1
Column:

A B C
[TABLE="width: 257"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Bean[/TD]
[TD="align: right"]$2.20 [/TD]
[TD] Supermarket[/TD]
[/TR]
[TR]
[TD]Soy[/TD]
[TD="align: right"]$1.90 [/TD]
[TD] Provision [/TD]
[/TR]
[TR]
[TD]Soya [/TD]
[TD="align: right"] $0.90[/TD]
[TD] 7-eleven[/TD]
[/TR]
[TR]
[TD]SoyaBean[/TD]
[TD="align: right"]$3.20[/TD]
[TD] 7-eleven[/TD]
[/TR]
[TR]
[TD]Soya Bean[/TD]
[TD="align: right"] $3.20 [/TD]
[TD] Giant[/TD]
[/TR]
[TR]
[TD]Sauce[/TD]
[TD="align: right"] $5.60[/TD]
[TD] Giant[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Word to find: [/TD]
[TD] Soy[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2:
[TABLE="width: 228"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]Provision Shop[/TD]
[TD="align: right"] $1.90 [/TD]
[TD] Soy[/TD]
[/TR]
[TR]
[TD]7-eleven[/TD]
[TD="align: right"] $0.90 [/TD]
[TD] Soya[/TD]
[/TR]
[TR]
[TD] 7-eleven[/TD]
[TD="align: right"] $3.20[/TD]
[TD] SoyaBean[/TD]
[/TR]
[TR]
[TD]Giant[/TD]
[TD="align: right"] $3.20 [/TD]
[TD] Soya Bean[/TD]
[/TR]
</tbody>[/TABLE]


Thank you so much for your time! And sorry for the trouble!

 

[hiker95]

Jesslynn,

Here is another macro solution for you to consider, that also uses the InputBox to ask for the word to search for, and, uses two arrays in memory to process the raw data, and, results.

I assume that worksheets Sheet1, and, Sheet2, already exist.

In this example the word that is entered into the InputBox is soy.

Sample raw data worksheet Sheet1:

Excel 2007
	A	B	C
1	Bean	$2.20	Supermarket
2	Soy	$1.90	Provision Shop
3	Soya	$0.90	7-eleven
4	SoyaBean	$3.20	7-eleven
5	Soya Bean	$3.20	Giant
6	Sauce	$5.60	Giant
7
Sheet1

After the macro in worksheet Sheet2:

Excel 2007
	A	B	C
1	Provision Shop	$1.90	Soy
2	7-eleven	$0.90	Soya
3	7-eleven	$3.20	SoyaBean
4	Giant	$3.20	Soya Bean
5
Sheet2

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).

Option Compare Text
Sub FindWord_V2()
' hiker95, 07/29/2015, ME871409
Dim a As Variant, o As Variant
Dim i As Long, j As Long
Dim Word As String
Word = InputBox("Type in the word that you want to find.")
Application.ScreenUpdating = False
With Sheets("Sheet1")
  a = .Range("A1:C" & .Cells(Rows.Count, "A").End(xlUp).Row)
  ReDim o(1 To UBound(a, 1), 1 To UBound(a, 2))
End With
For i = 1 To UBound(a, 1)
  If InStr(a(i, 1), Word) Then
    j = j + 1: o(j, 1) = a(i, 3): o(j, 2) = a(i, 2): o(j, 3) = a(i, 1)
  End If
Next i
With Sheets("Sheet2")
  .Columns(1).Resize(, 3).Clear
  .Cells(1, 1).Resize(UBound(o, 1), UBound(o, 2)) = o
  .Columns(1).Resize(, 3).AutoFit
  .Activate
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 FindWord_V2 macro.

 

[Jesslynn]

Thank you so much for the code! It really helped me alot! And all the little tips written! It can really help all the newbies here

 

[hiker95]

Jesslynn,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.