Need VBA code for macro to carry out Proper on activesheet

[Brandonfromsingapore]

Hello Forum,
I know there is a non-macro to do this and it is rather simple but i was hoping to see if i can also incorporate this into a macro?

I have an excel workbook with several sheets.

I need the following to be done on the active sheet



In this sheets, the header is on row 1

On the header that says “First Name” and “Middle Name(s)” and “Last Name”
I need the data in the rows below these header to be Proper (=proper(). This needs to be done on all the applicable rows until the last row with data (the number of rows is not fixed)

The problem that I face now is that I often get wrong capitalisation of the names. Sometimes they are in all caps, sometimes they are in all small alphabets. It is really kinda messy. I was hoping for a macro to do this and then I can assign the shortcut of “Ctrl” + “e”




Thank you

 

[JackDanIce]

Code might be:

Sub ProperCase()

Dim i as Long, j as Long
Dim Cell as Range

Application.ScreenUpdating = False

For i = 1 to 3
  j = Cells(rows.count, i).End(xlUp).Row
  For Each Cell in Range(Cells(1,i), Cells(j, i))
    Cell.Value = Application.Proper(Cell)
  Next Cell
Next i

Application.ScreenUpdating = True

End Sub

 

[Brandonfromsingapore]

Code might be:

Sub ProperCase()

Dim i as Long, j as Long
Dim Cell as Range

Application.ScreenUpdating = False

For i = 1 to 3
  j = Cells(rows.count, i).End(xlUp).Row
  For Each Cell in Range(Cells(1,i), Cells(j, i))
    Cell.Value = Application.Proper(Cell)
  Next Cell
Next i

Application.ScreenUpdating = True

End Sub

Hello Jack! Thank you but it does not seem to be correct. They aren't in the first column to the third column. They column reference aren't fix. I need the macro to be able to identify the correct column to proceed based on the column headers which can be found on row 1. Thanks.

 

[yky]

Insert a temp column, fill in the formula "=proper(rc[-1])" or whatever the address would be, copy that column, paste by value to rc[-1], delete the temp column.