Hi, this is a little embarrasssing. I have recorded a macro to capitalise a single letter in a range of cells. I seem to be having a little trouble with assigning the variable to proper.
This is my code
I keep getting the error on this line, I have changed it many ways but can not get it right.
Letter = Worksheets("ServiceDeliveryTemplate").Range("F7:F21").Text.Proper(" ")
Please Login or Register to view this content.
I can't see how you have an ActiveCell your code selects a range of cells, which cell are you working with?
To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.
If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.
Last edited by royUK; 06-28-2010 at 07:08 AM.
Hope that helps.
RoyUK
--------
For Excel Tips & Solutions, free examples and tutorials why not check out my web site
Free DataBaseForm example
What is that line supposed to do?
The line is supposed to take the string input which will be a letter and capitalise it. Simple really. Each cell in the range specified needs to be capitalised.Originally Posted by StephenR
That looks like made-up syntax. Why not just use the PROPER formula? And perhaps address Roy's post.
I recorded the macro and then tried to edit it to get it to work. I am trying the Proper function its not working for me yet though.
I will study up some more and will get ti should be easy get a range of letters to capitalise. Maybe just a bit late here.
If you have "fred" in A1 then in another cell =PROPER(A1) will yield "Fred".
Agree but thats where variations of the line aren't working for me.
Letter = Worksheets("ServiceDeliveryTemplate").Range("F7:F21").Text.Proper(" ")
So I am defining "Letter" at the start as a String and then telling it that the strings are the range of cells Range("F7:F21") ((Well Trying)) and thenI want to pass through Proper.
However if Change it to
Letter = Worksheets("ServiceDeliveryTemplate").Range("F7:F21").Text
Letter.Proper(" ")
it is still an error because i am not getting what the "expression" is. Because help defines the Proper Function as
Please Login or Register to view this content.
Last edited by flebber; 06-28-2010 at 10:07 AM. Reason: clarity
You can't do that - you need to loop through the cells to alter them:
Please Login or Register to view this content.
Everyone who confuses correlation and causation ends up dead.
Thanks a million that makes sense. I am going to look up and see how you used Proper in the statement "vbPropercase".
I used VBA's StrConv (String Conversion) function, since you're using VBA anyway. You could have used the Worksheet function from code, but it's slower - you would replace:
with either:Please Login or Register to view this content.
or:Please Login or Register to view this content.
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks