Excel 2007 : Macro uses the same number of cells when proper case

**emil9216** · 05-05-2011, 08:02 AM

I am trying to make macro for proper case the text in column A but the problem is that the macro uses the same number of cells i used to record the macro. For example, if i record the macro with 10 cells that have text in column A after this if i put a different text in 40 cells in column A and run the macro it will proper case only the first 10 cells with text. How to fix this? I want to make macro that will proper case all the cells from column A no matter if every time there is a different number of cells with text.

Thanks!

**sweep** · 05-05-2011, 08:08 AM

Hi,

This

Please Login or Register  to view this content.

will select all the cells in column A that contain something that's been directly entered (not the result of a formula). Is that of any use?

**emil9216** · 05-05-2011, 08:27 AM

Is it possible to do this just with formulas because i am not very familiar with coding macros and i need to record and other operations after proper casing?

**Marcol** · 05-05-2011, 09:05 AM

Try this

Please Login or Register  to view this content.

[EDIT]
Missed your last post

Try

Please Login or Register  to view this content.

If your column has numbers that must be kept as numbers not text, then

Please Login or Register  to view this content.

**emil9216** · 05-05-2011, 09:36 AM

This macro is good, but i need to proper case only the first letters of the words. But after this i want to add other things like, insert dashes between the words, combine B and C column. I know how to do these two operations but how to add them to the proper case macro?

This formula =UPPER(A2) doesn't make any change. It still uses the same number of cells.

**Marcol** · 05-05-2011, 10:03 AM

You would do best to post a sample workbook showing your Sheet Layout With Before and After examples.

It should clearly illustrate your problem and not contain any sensitive data.

**sukumar15** · 05-05-2011, 10:41 AM

hai try this code

Sub uppercase()
Dim cell As Range
For Each cell In Selection.Cells
If cell.HasFormula = False Then
cell = UCase(cell)
End If
Next
End Sub

**emil9216** · 05-05-2011, 10:42 AM

This is a sample macro i have recorded with 10 names in column A. If i paste text in more than 10 cells it will proper case only the text in the first 10 cells.

**Marcol** · 05-05-2011, 11:30 AM

Apologies for my earlier post, somehow I thought you wanted Uppercase

I don't see why you want to do this with a macro. However

Please Login or Register  to view this content.

This will put the formula you ask for in Column B for each used row in Column A

This will convert Column A to Proper Case

Please Login or Register  to view this content.

If you put this in B1 and drag it down you will get the same result as the first macro.

Please Login or Register  to view this content.

Hope this helps.

**emil9216** · 05-05-2011, 01:14 PM

Thanks, the first macro works great. I am trying to add the other functions to the proper case macro but i have the same problem. After proper casing I want to insert dashes between the words and then combine the B and C column but it works only for 10 cells. I have uploaded an example.

**Marcol** · 05-05-2011, 02:13 PM

Try this

Please Login or Register  to view this content.

Hope this helps.

**emil9216** · 05-05-2011, 03:05 PM

Thanks! The macro works great.

**Marcol** · 05-05-2011, 03:06 PM

Happy to have helped

Excel 2007 : Macro uses the same number of cells when proper case

LinkBack

Thread Tools

Rate This Thread

Display

Macro uses the same number of cells when proper case

Re: Macro uses the same number of cells when proper case

Re: Macro uses the same number of cells when proper case

Re: Macro uses the same number of cells when proper case

Re: Macro uses the same number of cells when proper case

Re: Macro uses the same number of cells when proper case

Re: Macro uses the same number of cells when proper case

Re: Macro uses the same number of cells when proper case

Re: Macro uses the same number of cells when proper case

Re: Macro uses the same number of cells when proper case

Re: Macro uses the same number of cells when proper case

Re: Macro uses the same number of cells when proper case

Re: Macro uses the same number of cells when proper case

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions