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

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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
 
Upvote 0
Thanks CV.

How do I insert MatchCase:=True in your code so that I am able to search upper / lower case?
 
Upvote 0
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:
Upvote 0
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>
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

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
Back
Top