How to re/name a cell if another cell contains X [Solved/Closed]

Posts
3
Registration date
Thursday January 7, 2016
Last seen
January 8, 2016
- - Latest reply: vcoolio
Posts
1236
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
March 1, 2019
- Jan 8, 2016 at 03:14 PM
Hello,

Apologies if this is a simple question but having some trouble figuring it out.

I have say 1000 rows of data and I need to label them all in column A which is blank.

How do I name or rename column A to say "XX" if column S contains the word "Wales" for example.

I will have other things in other columns but will deal with that once I've sorted this issue. So far I have this but can't figure out the renaming command:

Dim rCell As Range
Dim rRng As Range

Set rRng = Range("S:S")

For Each rCell In rRng.Cells
If UCase(rCell.Value) = "WALES" Then
      • Rename Cell A of the same Row to "XX"***



End If
Next rCell

Dim LastRow As Long
With ActiveSheet
LastRow = .Range("C:C").SpecialCells(xlCellTypeLastCell).Row

End With

Any help would be much appreciated.

Thanks,
See more 

3 replies

Best answer
Posts
1236
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
March 1, 2019
220
9
Thank you
Hello Alexzandeur,

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

Say "Thank you" 9

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

CCM 3253 users have said thank you to us this month

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

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!
Posts
1236
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
March 1, 2019
220
7
Thank you
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:-


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.
Alexzandeur
Posts
3
Registration date
Thursday January 7, 2016
Last seen
January 8, 2016
-
Thanks Vcoolio this works exactly as hoped. The only issue I'm having now is if I wanted to look at multiple conditions like if S2 contains "WALES" and T2 contains "YES" then change A2 to "YY" (Then cycle through rows)

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,
Posts
1236
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
March 1, 2019
220
1
Thank you
Hello Alexzandeur,

That's excellent! I'm glad that I was able to help.

Cheerio,
vcoolio.