just a quick question about this =proper(B2)

[rholdren]

I was just curious if it was possible to turn this statement around. For example
if I am in cell C2 and I want it to place the proper of john smith (which is in C1) in C2 I would type =proper(C1) and it would place John Smith in C2

what I was wondering was if it was possible to somehow switch the code around so that I would change the contents of C1 from john smith to John Smith
instead of having John Smith duplicated in cell C2

I have been trying to take some of the things I've learned here and use them and switch them around (helps me understand a little more) when I came across =Proper and thought to myself that could be handy under certain circumstances and I began to play around with it. Then I tried to switch it around to see if I could get C1 to change instead of placing the Correct capitalization in C2 but had no success. Just curious if there was a way.

Thanks

 

[JoeMo]

Can be done with VBA. If you want to stick to the worksheet formula, you could copy C2 and do a pastespecial values in C1

 

[alansidman]

You will need a VBA solution for that

Option Explicit


Sub Prop()
    Dim i As Long, lr As Long
    lr = Range("C" & Rows.Count).End(xlUp).Row
    For i = 1 To lr
        Range("C" & i) = Application.WorksheetFunction.Proper(Range("C" & i))
    Next i
End Sub

Standard Module
How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)


To run the Excel VBA code:
Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button

 

[rholdren]

Yea I thought that too but was just curious if it could be done without writing code. There have been times where I have written some long, drawn out subs and thought "hey, cool, that worked" and then I find out later that I could have accomplished the same thing with like one macro. I appreciate the response though. You get the best people here no doubt.

 

[rholdren]

that works and pretty simple. Thanks for that. Greatly appreciated.