Find Value in Column A & Enter Another Value in Column F

dgr

Board Regular
Joined
Apr 24, 2005
Messages
176
Hi,
I'm using Excel 2007. I'm looking for a vb macro solution.

I want to find the word Apple anywhere in Column A. If found I want to enter the word Boy in the same row in Column F. How do I achieve this?

Thanks for your help.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Carlos Vandango

New Member
Joined
Feb 5, 2015
Messages
16
Hi dgr,

The below code can do what you are after:

Code:
Sub findString()

Dim myLastRow As Integer
Dim myRow As Integer

myLastRow = Sheet("Sheet1").UsedRange.Rows.Count 'Change the name of the sheet here
myRow = 2

Do Until myRow = myLastRow

    If Sheets("Sheet1").Cells(myRow, 1) = "Apple" Then 'Change the strings you want to find and input
        Sheets("Sheet1").Cells(myRow, 6) = "Boy"
    End If
    
myRow = myRow + 1
Loop

End Sub

Hope this helps

Thanks,
CV
 

dgr

Board Regular
Joined
Apr 24, 2005
Messages
176
Thanks CV.

How do I insert MatchCase:=True in your code so that I am able to search upper / lower case?
 

dgr

Board Regular
Joined
Apr 24, 2005
Messages
176
No worries, CV.
I fixed it by changing Sheet to Sheets.
As for the upper / lower case, I realised that it's not necessary after all as your code recognises upper / lower case anyway.
Thanks for your help. I appreciate it.
 
Last edited:

dgr

Board Regular
Joined
Apr 24, 2005
Messages
176

ADVERTISEMENT

Oops, looks like this code solved half my problem only. My mistake for not providing detailed explanation.

How do I do a partial search using perhaps xlPart? For example, in Column A, the string I'm looking for could be:
<b>big red apple</b>
<b>apple cider vinegar</b>
 

Carlos Vandango

New Member
Joined
Feb 5, 2015
Messages
16
Hi dgr,

you could change this line of code:

Code:
If Sheets("Sheet1").Cells(myRow, 1) = "Apple" Then 'Change the strings you want to find and input

to something like this:

Code:
If Sheets("Sheet1").Cells(myRow, 1) LIKE "*Apple*" Then 'Change the strings you want to find and input

Thanks,
CV
 

dgr

Board Regular
Joined
Apr 24, 2005
Messages
176
Wow, thanks. Yes, this is great help. I appreciate it.
 

Forum statistics

Threads
1,141,312
Messages
5,705,682
Members
421,406
Latest member
kluna90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top