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

Question

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,

vcoolio · Accepted Answer

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

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.

vcoolio · Answer

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.

vcoolio · Answer

Hello Alexzandeur,

That's excellent! I'm glad that I was able to help.

Cheerio,
vcoolio.

How to re/name a cell if another cell contains X

3 responses

Similar discussions

Subscribe To Our Newsletter!