I have a sheet in which there are text matters.

I want to run a code, so that all the cells of the worksheet can be effected
to change the first letter of the cell into capital.

I was able to know with the web helps examples, by that I can do it manually
with the cell reference one by one. the formula is as below :

=CONCATENATE(UPPER(LEFT(A1,1)),LOWER(RIGHT(A1,LEN( A1)-1)))

But I want to run in a code for the complete sheet, so once I run the code
and it can be effective for all the sheet

pls. advice

Irshad

Dim cell As Range

For Each cell In ActiveSheet.UsedRange

If cell.Value < "" Then

cell.Value = UCase(Left(cell.Value, 1)) & Right(cell.Value,
Len(cell.Value) - 1)
End If
Next cell


--
__________________________________
HTH

Bob

"Irshad Alam" wrote in message
...
I have a sheet in which there are text matters.

I want to run a code, so that all the cells of the worksheet can be
effected
to change the first letter of the cell into capital.

I was able to know with the web helps examples, by that I can do it
manually
with the cell reference one by one. the formula is as below :

=CONCATENATE(UPPER(LEFT(A1,1)),LOWER(RIGHT(A1,LEN( A1)-1)))

But I want to run in a code for the complete sheet, so once I run the code
and it can be effective for all the sheet

pls. advice

Irshad

Why not

cell.Value = WorksheetFunction.Proper(cell.Value)

Stefi


€žBob Phillips€� ezt Ã*rta:

Dim cell As Range

For Each cell In ActiveSheet.UsedRange

If cell.Value < "" Then

cell.Value = UCase(Left(cell.Value, 1)) & Right(cell.Value,
Len(cell.Value) - 1)
End If
Next cell


--
__________________________________
HTH

Bob

"Irshad Alam" wrote in message
...
I have a sheet in which there are text matters.

I want to run a code, so that all the cells of the worksheet can be
effected
to change the first letter of the cell into capital.

I was able to know with the web helps examples, by that I can do it
manually
with the cell reference one by one. the formula is as below :

=CONCATENATE(UPPER(LEFT(A1,1)),LOWER(RIGHT(A1,LEN( A1)-1)))

But I want to run in a code for the complete sheet, so once I run the code
and it can be effective for all the sheet

pls. advice

Irshad

Partly because I would rather not call out to worksheet functions when
there is no need to, and partly because it doesn't do what the OP asks for.

--
__________________________________
HTH

Bob

"Stefi" wrote in message
...
Why not

cell.Value = WorksheetFunction.Proper(cell.Value)

Stefi


"Bob Phillips" ezt írta:

Dim cell As Range

For Each cell In ActiveSheet.UsedRange

If cell.Value < "" Then

cell.Value = UCase(Left(cell.Value, 1)) & Right(cell.Value,
Len(cell.Value) - 1)
End If
Next cell


--
__________________________________
HTH

Bob

"Irshad Alam" wrote in message
...
I have a sheet in which there are text matters.

I want to run a code, so that all the cells of the worksheet can be
effected
to change the first letter of the cell into capital.

I was able to know with the web helps examples, by that I can do it
manually
with the cell reference one by one. the formula is as below :

=CONCATENATE(UPPER(LEFT(A1,1)),LOWER(RIGHT(A1,LEN( A1)-1)))

But I want to run in a code for the complete sheet, so once I run the
code
and it can be effective for all the sheet

pls. advice

Irshad

Hi Bob,

Please explain disadvantages of calling worksheet functions when also other
means are available.

What is the difference between the two ways of changing the first letter to
capitals?

Thanks,
Stefi


€žBob Phillips€� ezt Ã*rta:

Partly because I would rather not call out to worksheet functions when
there is no need to, and partly because it doesn't do what the OP asks for.

--
__________________________________
HTH

Bob

"Stefi" wrote in message
...
Why not

cell.Value = WorksheetFunction.Proper(cell.Value)

Stefi


"Bob Phillips" ezt Ã*rta:

Dim cell As Range

For Each cell In ActiveSheet.UsedRange

If cell.Value < "" Then

cell.Value = UCase(Left(cell.Value, 1)) & Right(cell.Value,
Len(cell.Value) - 1)
End If
Next cell


--
__________________________________
HTH

Bob

"Irshad Alam" wrote in message
...
I have a sheet in which there are text matters.

I want to run a code, so that all the cells of the worksheet can be
effected
to change the first letter of the cell into capital.

I was able to know with the web helps examples, by that I can do it
manually
with the cell reference one by one. the formula is as below :

=CONCATENATE(UPPER(LEFT(A1,1)),LOWER(RIGHT(A1,LEN( A1)-1)))

But I want to run in a code for the complete sheet, so once I run the
code
and it can be effective for all the sheet

pls. advice

Irshad

Calling worksheet functions instead of staying inside of VBA is usually
(always???) slower.

And there's a difference if there are are multiple words in the cell.

And as well as ucase() and lcase(), VBA has its own strconv(), too.

Stefi wrote:

Hi Bob,

Please explain disadvantages of calling worksheet functions when also other
means are available.

What is the difference between the two ways of changing the first letter to
capitals?

Thanks,
Stefi

€žBob Phillips€� ezt Ã*rta:

Partly because I would rather not call out to worksheet functions when
there is no need to, and partly because it doesn't do what the OP asks for.

--
__________________________________
HTH

Bob

"Stefi" wrote in message
...
Why not

cell.Value = WorksheetFunction.Proper(cell.Value)

Stefi


"Bob Phillips" ezt Ã*rta:

Dim cell As Range

For Each cell In ActiveSheet.UsedRange

If cell.Value < "" Then

cell.Value = UCase(Left(cell.Value, 1)) & Right(cell.Value,
Len(cell.Value) - 1)
End If
Next cell


--
__________________________________
HTH

Bob

"Irshad Alam" wrote in message
...
I have a sheet in which there are text matters.

I want to run a code, so that all the cells of the worksheet can be
effected
to change the first letter of the cell into capital.

I was able to know with the web helps examples, by that I can do it
manually
with the cell reference one by one. the formula is as below :

=CONCATENATE(UPPER(LEFT(A1,1)),LOWER(RIGHT(A1,LEN( A1)-1)))

But I want to run in a code for the complete sheet, so once I run the
code
and it can be effective for all the sheet

pls. advice

Irshad







--

Dave Peterson

To add to what Dave said, if you are going to use a VBA solution, the
less worksheet calls, the better, because VBA has to do some behind-
the-scenes work to actually interface with Excel. It's not the fastest
way to do things. You want to limit your worksheet calls as much as
possible (when appropriate) to the times when you are reading data, or
writing data back to the worksheet.

--JP


On Sep 3, 8:10 am, Stefi wrote:
Hi Bob,

Please explain disadvantages of calling worksheet functions when also other
means are available.

What is the difference between the two ways of changing the first letter to
capitals?

Thanks,
Stefi

„Bob Phillips” ezt írta:

Partly because I would rather not call out to worksheet functions when
there is no need to, and partly because it doesn't do what the OP asks for.

--
__________________________________
HTH

Bob

"Stefi" wrote in message
...
Why not

cell.Value = WorksheetFunction.Proper(cell.Value)

Stefi

"Bob Phillips" ezt írta:

Dim cell As Range

For Each cell In ActiveSheet.UsedRange

If cell.Value < "" Then

cell.Value = UCase(Left(cell.Value, 1)) & Right(cell.Value,
Len(cell.Value) - 1)
End If
Next cell

--
__________________________________
HTH

Bob

"Irshad Alam" wrote in message
...
I have a sheet in which there are text matters.

I want to run a code, so that all the cells of the worksheet can be
effected
to change the first letter of the cell into capital.

I was able to know with the web helps examples, by that I can do it
manually
with the cell reference one by one. the formula is as below :

=CONCATENATE(UPPER(LEFT(A1,1)),LOWER(RIGHT(A1,LEN( A1)-1)))

But I want to run in a code for the complete sheet, so once I run the
code
and it can be effective for all the sheet

pls. advice

Irshad

As has been mentioned, there is an overhead to calling a worksheetfunction,
as you are calling out to Excel. That means all of the appropriate
information is passed to Excel (via the inefficient COM inerface?) and then
Excel has to do the function (which is probably very quick), and then Excel
has to pass it back via the same channel. This process is costly, and whilst
acceptable for one or tow instances, in a loop it doesn't sound like a good
idea to me.

The OP also said that they wanted to change the first letter of the cell,
not the first letter of every word in the cell as Proper would do.

--
__________________________________
HTH

Bob

"Stefi" wrote in message
...
Hi Bob,

Please explain disadvantages of calling worksheet functions when also
other
means are available.

What is the difference between the two ways of changing the first letter
to
capitals?

Thanks,
Stefi


"Bob Phillips" ezt írta:

Partly because I would rather not call out to worksheet functions when
there is no need to, and partly because it doesn't do what the OP asks
for.

--
__________________________________
HTH

Bob

"Stefi" wrote in message
...
Why not

cell.Value = WorksheetFunction.Proper(cell.Value)

Stefi


"Bob Phillips" ezt írta:

Dim cell As Range

For Each cell In ActiveSheet.UsedRange

If cell.Value < "" Then

cell.Value = UCase(Left(cell.Value, 1)) &
Right(cell.Value,
Len(cell.Value) - 1)
End If
Next cell


--
__________________________________
HTH

Bob

"Irshad Alam" wrote in message
...
I have a sheet in which there are text matters.

I want to run a code, so that all the cells of the worksheet can be
effected
to change the first letter of the cell into capital.

I was able to know with the web helps examples, by that I can do it
manually
with the cell reference one by one. the formula is as below :

=CONCATENATE(UPPER(LEFT(A1,1)),LOWER(RIGHT(A1,LEN( A1)-1)))

But I want to run in a code for the complete sheet, so once I run
the
code
and it can be effective for all the sheet

pls. advice

Irshad

Hi Bob

Not sure in what way the Proper function doesn't achieve what the OP
requests.

--
Regards
Roger Govier

"Bob Phillips" wrote in message
...
Partly because I would rather not call out to worksheet functions when
there is no need to, and partly because it doesn't do what the OP asks
for.

--
__________________________________
HTH

Bob

"Stefi" wrote in message
...
Why not

cell.Value = WorksheetFunction.Proper(cell.Value)

Stefi


"Bob Phillips" ezt írta:

Dim cell As Range

For Each cell In ActiveSheet.UsedRange

If cell.Value < "" Then

cell.Value = UCase(Left(cell.Value, 1)) & Right(cell.Value,
Len(cell.Value) - 1)
End If
Next cell


--
__________________________________
HTH

Bob

"Irshad Alam" wrote in message
...
I have a sheet in which there are text matters.

I want to run a code, so that all the cells of the worksheet can be
effected
to change the first letter of the cell into capital.

I was able to know with the web helps examples, by that I can do it
manually
with the cell reference one by one. the formula is as below :

=CONCATENATE(UPPER(LEFT(A1,1)),LOWER(RIGHT(A1,LEN( A1)-1)))

But I want to run in a code for the complete sheet, so once I run the
code
and it can be effective for all the sheet

pls. advice

Irshad

If The User Wants Every Word To Have Its First Letter Uppercase, Then Proper
Would Work.

But if the user wants just the first letter in the cell uppercase, then maybe
not.

But I'd have used:

cell.Value = UCase(Left(cell.Value, 1)) _
& lcase(Right(cell.Value, Len(cell.Value) - 1))

or

cell.Value = UCase(Left(cell.Value, 1)) & lcase(mid(cell.Value, 2)

to match the sample worksheet function the OP gave.

Roger Govier wrote:

Hi Bob

Not sure in what way the Proper function doesn't achieve what the OP
requests.

--
Regards
Roger Govier

"Bob Phillips" wrote in message
...
Partly because I would rather not call out to worksheet functions when
there is no need to, and partly because it doesn't do what the OP asks
for.

--
__________________________________
HTH

Bob

"Stefi" wrote in message
...
Why not

cell.Value = WorksheetFunction.Proper(cell.Value)

Stefi


"Bob Phillips" ezt írta:

Dim cell As Range

For Each cell In ActiveSheet.UsedRange

If cell.Value < "" Then

cell.Value = UCase(Left(cell.Value, 1)) & Right(cell.Value,
Len(cell.Value) - 1)
End If
Next cell


--
__________________________________
HTH

Bob

"Irshad Alam" wrote in message
...
I have a sheet in which there are text matters.

I want to run a code, so that all the cells of the worksheet can be
effected
to change the first letter of the cell into capital.

I was able to know with the web helps examples, by that I can do it
manually
with the cell reference one by one. the formula is as below :

=CONCATENATE(UPPER(LEFT(A1,1)),LOWER(RIGHT(A1,LEN( A1)-1)))

But I want to run in a code for the complete sheet, so once I run the
code
and it can be effective for all the sheet

pls. advice

Irshad






--

Dave Peterson

Because he/she asked for the first letter, not the first letter of every
word.

--
__________________________________
HTH

Bob

"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
Hi Bob

Not sure in what way the Proper function doesn't achieve what the OP
requests.

--
Regards
Roger Govier

"Bob Phillips" wrote in message
...
Partly because I would rather not call out to worksheet functions when
there is no need to, and partly because it doesn't do what the OP asks
for.

--
__________________________________
HTH

Bob

"Stefi" wrote in message
...
Why not

cell.Value = WorksheetFunction.Proper(cell.Value)

Stefi


"Bob Phillips" ezt írta:

Dim cell As Range

For Each cell In ActiveSheet.UsedRange

If cell.Value < "" Then

cell.Value = UCase(Left(cell.Value, 1)) & Right(cell.Value,
Len(cell.Value) - 1)
End If
Next cell


--
__________________________________
HTH

Bob

"Irshad Alam" wrote in message
...
I have a sheet in which there are text matters.

I want to run a code, so that all the cells of the worksheet can be
effected
to change the first letter of the cell into capital.

I was able to know with the web helps examples, by that I can do it
manually
with the cell reference one by one. the formula is as below :

=CONCATENATE(UPPER(LEFT(A1,1)),LOWER(RIGHT(A1,LEN( A1)-1)))

But I want to run in a code for the complete sheet, so once I run the
code
and it can be effective for all the sheet

pls. advice

Irshad