XL 97 Proper function 1

[AustinMan]

I have a problem with the proper function. 5 Stars for the one who can me with this.

Set X as Variant

If ActiveCell.Column = 2 Then
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Cursor = xlIBeam
Set X = Worksheets("Personnel&quot.Cells(1, ActiveCell.Column - 1)
Worksheets("Personnel&quot.X.Address.Value = Excel.Application.WorksheetFunction.Proper(X.Value)
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Cursor = xlDefault
End If

The bold line keeps error out. What gives.

Thank you in advance.

G.C.

 

[SkipVought]

Hi,
A couple of observations but first a question...

What is your objective with this code?

Observations
1. Dim X As Range instead of set X as variant

2.

Set X = Worksheets("Personnel").Cells(1, ActiveCell.Column - 1)

is equivalent to...

Set X = Worksheets("Personnel").Cells(1, 1)

since we have to be in column 2 to have gotten here.

3.

 Worksheets("Personnel").X.Address.Value

is not valid. Either X.Address or X.Value NOT X.Address.Value

So back to the question...

What is your objective with this code?


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Zathras]

It looks like you are trying to take the values in column "B" and set them to proper case.

Here is a bare-bones macro that you can experiment with.
[blue]

Option Explicit

Sub MakeProper()
Dim c As Range
  For Each c In Range("B2:B10")
    c.Value = StrConv(c.Value, 3)
  Next c
End Sub

[/color]

 

[TonyJollans]

AustinMan,

Nothing obviously wrong with Proper but quite a bit wrong with the rest of the statement. Try X.Value = WorksheetFunction.Proper(X.Value)

Enjoy,
Tony

 

[AustinMan]

Thanks to you all for your quick response. All help was appreciated.

I wanted to Proper case the active row only and Tony provided me with a solution.

Thanks Tony!!

 

[SkipVought]

Austinman,

As you have stated the problem X is defined as Cells(1,1)

What you need is...

With ActiveCell
   .Offset(0, -1).Value = Application.Proper(.Value)
End With

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com