Using a Named Range to Evaluate("INDEX(PROPER(

[Shiseiji]

Sorry, but I couldn't hit on the correct search term(s) for any previously posted answers.
Is it possible to replace the range address' in the code with the named range?
Obviously nothing I've tried has worked, it converts everything in the range to "#Value"

Sub m_MakeProper()
    '2/8/2018
    
    Application.Volatile True
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.DisplayStatusBar = False
    '
    Worksheets("test").Activate
    '
    Dim LastCol                       As Integer
    Dim LastRow                      As Long
    Dim thiswksht                    As Worksheet
    '
    Set thiswksht = ActiveSheet
'
    If thiswksht.AutoFilterMode Then
        AutoFilterMode = False
    End If
    '
    With thiswksht
        LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
        LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    End With
    '
    Cells(1, 1).Activate
    Rows(1).find("Status").Select
    ActiveCell.Offset(1, 0).Activate
    '
    Range(ActiveCell.Address, Cells(LastRow, ActiveCell.Column)).Select
    Selection.Name = "c_T_test1"
    '
    With Worksheets("test")
       LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
       .Range("D2:D" & LastRow).Value = .Evaluate("INDEX(PROPER(D2:D" & LastRow & "),)")
    End With
    '    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.DisplayStatusBar = True

End Sub

As always,
TIA for your time.

Ron

 

[JoeMo]

Might be helpful if you can elaborate on exactly what problem you are having. Haven't looked carefully at your code, but the piece (below) that changes col D entries to Proper case works fine for me (even if I leave the dot "." in front of Evaluate).

Sub try()
With Worksheets("test")
       LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
       .Range("D2:D" & LastRow).Value = Evaluate("INDEX(PROPER(D2:D" & LastRow & "),)")
    End With
End Sub

 

[Domenic]

My understanding is that you want to use a named range as a reference. If so, assuming that your range is named "MyNamedRange", try...

With Range("MyNamedRange")
    .Value = Application.Evaluate("INDEX(PROPER(" & .Address & "),0)")
End With

Although, INDEX isn't really necessary....

With Range("MyNamedRange")
    .Value = Application.Evaluate("PROPER(" & .Address & ")")
End With

Change the name of your range, accordingly.

Hope this helps!

 

[Shiseiji]

Thank you very much Domenic! As JoeMo indicated, I obviously wasn't very clear, but you managed to figure out what I was trying to ask.

JoMo, the code you referenced was working fine, I wanted to avoid cell addresses so the code is mobile through many places I will use it with standard column headings for a search, but not with reliable column order. Thanks for the pointer on "Index."

I stumbled across the "Application.Evaluate" but it looks like it is a really powerful tool. The way you set this up it can be used for many regular Excel formulas by simply setting a range.


Thanks again, not many rows in my test worksheet so it was neat to watch the strings all become proper case. I don't have to deal with "I'll" etc. so this is perfect!


Appreciate your time and sharing your expertise and JoMo for trying to figure out my question! Too many years of hacking at this and I still get stumped trying to figure out the proper terminology.


Ron