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

Shiseiji

Board Regular
Joined
Oct 23, 2009
Messages
214
Office Version
  1. 2019
Platform
  1. Windows
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"


Code:
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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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).
Code:
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
 
Upvote 0
My understanding is that you want to use a named range as a reference. If so, assuming that your range is named "MyNamedRange", try...

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

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

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

Change the name of your range, accordingly.

Hope this helps!
 
Last edited:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

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