Conditional change: Change value in a cell based on specific text (ending in "X") in another column

Bob_C

New Member
Joined
Nov 13, 2019
Messages
4
Hello Board,

I am new to board and dumb, hoping someone would share a simple VBA code

I have a large table of data of ~10000 rows.

I'd like VBA Code to loop through:
1) evaluate data in column A for cells that contains 5 characters ending in letter "X"
2) If it finds such, I'd like to change text in column C to "Group"

For Example: In this it would change C2 and C3 to "Group" since A2 and A3 ID#'s contain 5 letters and end in X

ABC
1ID#AMOUNTTYPE
2RBFCX5001
Single
3TLSHX4006Indiv
4VA98278542Single
5UDGSX9865Group

<tbody>
</tbody>



Thanks for any help
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,732
Office Version
365
Platform
Windows
Hi & welcome to MrExcel.
How about
Code:
Sub BobC()
    With Range("C2:C" & Range("A" & Rows.Count).End(xlUp).Row)
        .Value = Evaluate(Replace("If((len(@)=5)*(right(@)=""X""),""Group""," & .Address & ")", "@", .Offset(, -2).Address))
    End With
End Sub
 

Bob_C

New Member
Joined
Nov 13, 2019
Messages
4
Thanks for replying Fluff! Your code ran without error, unfortunately it did not replace anything in the worksheet.
 

Bob_C

New Member
Joined
Nov 13, 2019
Messages
4
FORGIVE ME FLUFF!!! I happened to add a column while experimenting. When I adjusted range it ran successfully.

Thank you very much!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,732
Office Version
365
Platform
Windows
Glad to help & thanks for the feedback
 

Forum statistics

Threads
1,085,544
Messages
5,384,352
Members
401,889
Latest member
Pmccollin

Some videos you may like

This Week's Hot Topics

Top