Alexzandeur

- Posts
- 3
- Registration date
- Thursday January 7, 2016
- Last seen
- January 8, 2016

- Posts
- 1236
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- March 1, 2019

Related:

- How to re/name a cell if another cell contains x
- How to find someone's location by cell phone number - How-To - Mobile
- If cell contains text then ✓ - Forum - Excel
- How to copy data from one cell to another in excel using formula - Forum - Office Software
- Excel if cell contains text then copy to another sheet ✓ - Forum - Excel
- Excel conditional formatting based on date in another cell - How-To - Excel

Best answer

vcoolio

- Posts
- 1236
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- March 1, 2019

Hello Alexzandeur,

Based on your above comment, I would say that the following should work for you:-

Following is a link to my test work book for you to peruse:-

https://www.dropbox.com/s/dbblligihczgt9v/Alexzandeur.xlsm?dl=0

Click on the button to see it work.

In Columns S & T, I've mixed up the upper and lower cases just to make sure that the code picks up on varying cases (and it does!).

You should be able to take it from here.

Cheerio,

vcoolio.

"if S2 contains "WALES" and T2 contains "YES" then change A2 to "YY" (Then cycle through rows)"

Based on your above comment, I would say that the following should work for you:-

Sub AllTheExs() Application.ScreenUpdating = False Dim lr As Long lr = Range("S" & Rows.Count).End(xlUp).Row For Each cell In Range("S2:S" & lr) If UCase(cell.Value) = "WALES" Then cell.Offset(, -18) = "XX" End If AllTheExsAndYs Next End Sub Sub AllTheExsAndYs() Dim lr As Long Dim i As Integer lr = Range("A" & Rows.Count).End(xlUp).Row For i = 2 To lr If UCase(Cells(i, 19).Value) = "WALES" And UCase(Cells(i, 20).Value) = "YES" Then Range(Cells(i, 1), Cells(i, 1)) = "YY" End If Next Application.ScreenUpdating = True End Sub

Following is a link to my test work book for you to peruse:-

https://www.dropbox.com/s/dbblligihczgt9v/Alexzandeur.xlsm?dl=0

Click on the button to see it work.

In Columns S & T, I've mixed up the upper and lower cases just to make sure that the code picks up on varying cases (and it does!).

You should be able to take it from here.

Cheerio,

vcoolio.

A few words of thanks would be greatly appreciated. Add comment

3253 users have said thank you to us this month

vcoolio

- Posts
- 1236
- Registration date
- Thursday July 24, 2014
- Status
- Moderator
- Last seen
- March 1, 2019

Hello Alexzandeur,

Perhaps a simple "IF" formula is all that you need:-

=IF(S2="","",IF(S2="Wales","XX"))

Place the formula in cell A2 and drag it down as far as you need.

But if you still prefer a macro, then the following modified version of yours should do the task also:-

I hope that this helps.

Cheerio,

vcoolio.

Perhaps a simple "IF" formula is all that you need:-

=IF(S2="","",IF(S2="Wales","XX"))

Place the formula in cell A2 and drag it down as far as you need.

But if you still prefer a macro, then the following modified version of yours should do the task also:-

Sub AllTheExs() Application.ScreenUpdating = False Dim lr As Long lr = Range("S" & Rows.Count).End(xlUp).Row For Each cell In Range("S2:S" & lr) If UCase(cell.Value) = "WALES" Then cell.Offset(, -18) = "XX" End If Next Application.ScreenUpdating = True End Sub

I hope that this helps.

Cheerio,

vcoolio.

I can manage to make it do it by looking at specific cells but can't get it to cycle through each one and change the A column accordingly, my version will look at T2 and then apply that to every cell in A column.

Sorry to be a pain but once I have the next step I should be able to duplicate it for any column I need.

The help is very much appreciated.

Thanks,

Used your extras and have managed to duplicate it to look at dozens of columns all working perfectly!

You've saved me hours of work, very much appreciated.

Thanks again!