Change result to Upper Case

Livin404

Well-known Member
Joined
Jan 7, 2019
Messages
743
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
VBA Code:
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
 
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 ;)
I did try that, but in the original code to get the applicaton.username they have to match with what is in the Array so it's caps sensitive. Before I was getting just the Sir Name if they did not match.
 
Upvote 0

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
I did try that, but in the original code to get the applicaton.username they have to match with what is in the Array so it's caps sensitive. Before I was getting just the Sir Name if they did not match.
I see. It is because the InStr default. Format is

InStr( [start], string, substring, [compare] )

You have If InStr(WorkName, x) > 0 Then where you only filled the bold part .... InStr( [start], string, substring, [compare] )
So, [start] is default to 1 (1st letter) and compare default to 0 (Binary comparison which is case sensitive)

You can write as

If InStr(WorkName, x, 1) or
If InStr(WorkName, x, vbTextCompare)

then it will not be case sensitive

Option for [compare]
vbUseCompareOption -1
vbBinaryCompare 0
vbTextCompare 1
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top