Change result to Upper Case

[Livin404]

Greetings, I had great help from Eric last night, now I just need one small thing. I'm trying to figure out how I can change the end result to be in all capital letters. Basically after the formula I need the ranks found in the array to be in all CAPS.
Thank you

S

ub find_last_plus_3()
Dim LastRow As Long, WorkName As String, Title As String, x As Variant

    LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    WorkName = Replace(Application.UserName, "GS-05", "Mr")
    Title = ""
    For Each x In Array("MR ", "Maj ", "Cpt ", "Col ")
        If InStr(WorkName, x) > 0 Then
            Title = x
            Exit For
        End If
    Next x
    Range("A" & LastRow + 3).Value = "UPDATED BY: " & Title & Split(WorkName, ",")(0)
End Sub

 

[Zot]

You just need to put UCase(<string_variable>) where needed

Example UCase("Good Morning") will give you GOOD MORNING

 

[Livin404]

You just need to put UCase(<string_variable>) where needed

Example UCase("Good Morning") will give you GOOD MORNING

I had tried it always I place it right at the bottom before End Sub and nothing happened. Thank you,

Sub find_last_plus_3()
Dim LastRow As Long, WorkName As String, Title As String, x As Variant

    LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    WorkName = Replace(Application.UserName, "GS-05", "Mr")
    Title = ""
    For Each x In Array("MR ", "Maj ", "Cpt ", "Col ")
        If InStr(WorkName, x) > 0 Then
            Title = x
            Exit For
        End If
    Next x
    Range("A" & LastRow + 3).Value = "UPDATED BY: " & Title & Split(WorkName, ",")(0)
UCase ("*UPDATED BY: ")
End Sub

 

[Zot]

If you want to UCase everything in range Range("A" & LastRow + 3), then just

UCase (Range("A" & LastRow + 3))

Check if this works

 

[Livin404]

If you want to UCase everything in range Range("A" & LastRow + 3), then just

UCase (Range("A" & LastRow + 3))

Check if this works

I did try it right in the line before End Sub, and I'm afraid nothing happened. I didn't get any errors but nothing changed. I end up with UPDATED BY: Capt WHITESIDE, where the result should be UPDATED BY: CAPT WHISTESIDE. Remember the names and ranks will change depending on the "username". Thank you

 

[Akuini]

Try:

Range("A" & LastRow + 3).Value = "UPDATED BY: " & UCase(Title) & UCase(Split(WorkName, ",")(0))

 

[Zot]

So the Title needs to be upper case. Try

Range("A" & LastRow + 3).Value = "UPDATED BY: " & UCase(Title) & Split(WorkName, ",")(0)

I did not fully understand I guess when you said Remember the names and ranks will change depending on the "username"

When is title need not to be upper case?

@Akuini code should work. I did not change the name to upper case here

 

[Livin404]

Try:

Range("A" & LastRow + 3).Value = "UPDATED BY: " & UCase(Title) & UCase(Split(WorkName, ",")(0))

Try:

Range("A" & LastRow + 3).Value = "UPDATED BY: " & UCase(Title) & UCase(Split(WorkName, ",")(0))

That is brilliant works great thank you so much!

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.

 

[Zot]

I was wondering why you did not just put everything in CAPS like

For Each x In Array("MR ", "MAJ ", "CPT ", "COL ")

then no need to use UCase