Do PROPER case update, while pasting array, HOWTO?

[Lexje]

Hi Everybody,

I'm working with an array as follows:

Dim cellRefs() As Variant
'Code to make a selection

    I = 1
    For Each cell In Selection
        'stores cell values in the array
        cellRefs(I) = cell.Value
    I = I + 1
    Next

'Code to add newsheet after activesheet and paste array to newsheet

    Sheets.Add After:=ActiveSheet
    Cells(2, 1).Select
    For I = 1 To numberOfCells
        ActiveCell.Value = cellRefs(I)
        ActiveCell.Offset(1, 0).Select
    Next I

This works allright, but I'd like to correct the text in the cells by converting it to PROPER case.
How would I add this to the above code?

Thanks for your help!!

Erwin

 

[mdusoe]

Try this:

    For I = 1 To numberOfCells 
        ActiveCell.Value = WorksheetFunction.Proper(cellRefs(I)) 
        ActiveCell.Offset(1, 0).Select 
    Next I

 

[Lexje]

Thanks mdusoe!
Any way to do this while storing content in the array?
I ask this, because the array content is also used to fill a listbox.

Thanks a lot for your help!!

Erwin

 

[NateO]

The following worked for me:

Dim myArr As Variant
Let myArr = Evaluate("transpose(proper(" & Selection.Address & "))")
MsgBox myArr(2)

You might want to test whether the selection is multi-celled and whether the range is vertical or horizontal... You may need to loop.

If you're looping through cells, try:

myArr(x) = StrConv(myRng.Value, vbProperCase)

 

[mdusoe]

To use my scheme, but change it "on input", if you will, put this code into the first part...

For Each cell In Selection 
        'stores cell values in the array 
        cellRefs(I) = WorksheetFunction.Proper(cell.Value) 
    I = I + 1 
    Next

Now, your array has the case switched already, and you don't the Proper method on the paste part...

HTH,
Mike.

 

[Lexje]

Thanks a lot guys!!
This forrum really ROCKS!!!
Thanks a lot for the precious help

Erwin