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

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

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

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

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

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

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

Absolutely right, Bob. I hadn't thought of he cells possibly containing
multiple words.

--
Regards
Roger Govier

"Bob Phillips" wrote in message
...
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

Sir,
I have checked the VB code you advised(the above first reply), it is not
same what i require, it is making the all the capital to all the first word
of that cell-

The code you advised was :
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

Please note that my requirement is to make only the first word first letter
capital only
Exactly the result which this formula gives

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

My requirement is exactly the result of the above formula BUt for all the
sheet.

Regards

Irshad




"Bob Phillips" wrote:

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

Thank all of you for the detailed explanation!
Stefi


€žBob Phillips€� ezt Ã*rta:

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

Sorry, but it just doesn't do that, something else must be happening.

--
__________________________________
HTH

Bob

"Irshad Alam" wrote in message
...
Sir,
I have checked the VB code you advised(the above first reply), it is not
same what i require, it is making the all the capital to all the first
word
of that cell-

The code you advised was :
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

Please note that my requirement is to make only the first word first
letter
capital only
Exactly the result which this formula gives

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

My requirement is exactly the result of the above formula BUt for all the
sheet.

Regards

Irshad




"Bob Phillips" wrote:

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

I'd use:
cell.Value = UCase(Left(cell.Value, 1)) & lcase(mid(cell.Value, 2)



Irshad Alam wrote:

Sir,
I have checked the VB code you advised(the above first reply), it is not
same what i require, it is making the all the capital to all the first word
of that cell-

The code you advised was :
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

Please note that my requirement is to make only the first word first letter
capital only
Exactly the result which this formula gives

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

My requirement is exactly the result of the above formula BUt for all the
sheet.

Regards

Irshad


"Bob Phillips" wrote:

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









--

Dave Peterson

sorry for late reply, was out.

I have re-checked my above posting works fine, the same type in Microsoft
word if you start a sentence by typing small letters, when you move to next
line pressing enter, it automatically makes the first letter of the sentence
capital.

Exactly this is my requirement which I am able to solve by the indiviual
cell by this formula:

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

But I want a vba, so that once I run, it will work for the complete
selection for making all the cells first letter (of first word) into capital.

Regards

Irshad


"Bob Phillips" wrote:

Sorry, but it just doesn't do that, something else must be happening.

--
__________________________________
HTH

Bob

"Irshad Alam" wrote in message
...
Sir,
I have checked the VB code you advised(the above first reply), it is not
same what i require, it is making the all the capital to all the first
word
of that cell-

The code you advised was :
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

Please note that my requirement is to make only the first word first
letter
capital only
Exactly the result which this formula gives

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

My requirement is exactly the result of the above formula BUt for all the
sheet.

Regards

Irshad




"Bob Phillips" wrote:

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

You want it to occur when you hit ENTER key use this event code adapted from
Bob's code.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A10" 'adjust to suit
Dim cell As Range
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
For Each cell In Target
If cell.Value < "" Then
cell.Value = UCase(Left(cell.Value, 1)) & Right(cell.Value, _
Len(cell.Value) - 1)
End If
Next cell
End If
ws_exit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste into that sheet module. Edit to suit the range.

Alt + q to return to the Excel window.


Gord Dibben MS Excel MVP

On Sat, 6 Sep 2008 22:03:01 -0700, Irshad Alam
wrote:

sorry for late reply, was out.

I have re-checked my above posting works fine, the same type in Microsoft
word if you start a sentence by typing small letters, when you move to next
line pressing enter, it automatically makes the first letter of the sentence
capital.

Exactly this is my requirement which I am able to solve by the indiviual
cell by this formula:

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

But I want a vba, so that once I run, it will work for the complete
selection for making all the cells first letter (of first word) into capital.

Regards

Irshad


"Bob Phillips" wrote:

Sorry, but it just doesn't do that, something else must be happening.

--
__________________________________
HTH

Bob

"Irshad Alam" wrote in message
...
Sir,
I have checked the VB code you advised(the above first reply), it is not
same what i require, it is making the all the capital to all the first
word
of that cell-

The code you advised was :
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

Please note that my requirement is to make only the first word first
letter
capital only
Exactly the result which this formula gives

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

My requirement is exactly the result of the above formula BUt for all the
sheet.

Regards

Irshad




"Bob Phillips" wrote:

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