Report

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

Ask a question Alexzandeur 3Posts Thursday January 7, 2016Registration date January 8, 2016 Last seen - Latest answer on Jan 8, 2016 03:14PM
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 
Helpful
+9
moins plus
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.
Alexzandeur 3Posts Thursday January 7, 2016Registration date January 8, 2016 Last seen - Jan 8, 2016 09:39AM
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!
Reply
Add comment
Helpful
+7
moins plus
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 3Posts Thursday January 7, 2016Registration date January 8, 2016 Last seen - Jan 8, 2016 03:22AM
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,
Reply
Add comment
Helpful
+1
moins plus
Hello Alexzandeur,

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

Cheerio,
vcoolio.
Add comment

Members get more answers than anonymous users.

Being a member gives you detailed monitoring of your requests.

Being a member gives you additional options.

Not a member yet?

sign-up, it takes less than a minute and it's free!