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

[Bob_C]

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

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ID#[/TD]
[TD]AMOUNT[/TD]
[TD]TYPE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]RBFCX[/TD]
[TD]5001
[/TD]
[TD]Single[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]TLSHX[/TD]
[TD]4006[/TD]
[TD]Indiv[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]VA9827[/TD]
[TD]8542[/TD]
[TD]Single[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]UDGSX[/TD]
[TD]9865[/TD]
[TD]Group[/TD]
[/TR]
</tbody>[/TABLE]



Thanks for any help

 

[Fluff]

Hi & welcome to MrExcel.
How about

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]

Thanks for replying Fluff! Your code ran without error, unfortunately it did not replace anything in the worksheet.

 

[Bob_C]

FORGIVE ME FLUFF!!! I happened to add a column while experimenting. When I adjusted range it ran successfully.

Thank you very much!

 

[Bob_C]

Did in fact work

 

[Fluff]

Glad to help & thanks for the feedback