Converting Worksheet To Proper-Case
Sep 9, 2009I have an Excel 2003 worksheet with all the data in it in Upper-case.
I found this VBA code which works if you change a cell.
ADVERTISEMENT
Proper Case/Sentence Case In Macro Code
May 8, 2008Sub Addy()
Do Until ActiveCell. Offset(0, -4) = ""
Renamer = Proper(ActiveCell)
ActiveCell = Renamer
ActiveCell.Offset(1, 0).Select
Loop
End Sub
fail? Trying to remove all capitals from names/addresses. Error message is "compile error - sub or function not defined"
VBA Convert To Proper Case
Jun 30, 2013I've had good success with the following line of code
Code:
Sheets("Test").Range("A2") = UCase(Sheets("Test").Range("A1"))
But how do I do the same, converting the to Proper Case?
Proper Case In A Macro
Feb 21, 2007I have just recently found that I can do case correction with Excel but I am manually having to do it how can I add it to my macro? The function for doing it does not seem straight forward to me on putting in macro I am sure it is simple but just missing some element of it.
I need to have Proper case for columns C, G and H from rows 11 and down.
Use Proper Case Only For Input Box
May 30, 2007how can i change the text put into an input box into proper case regardless of what my user puts in?
View 9 Replies View RelatedChange To Proper Case In VBA
Aug 10, 2009I found this bit of Worksheet_Change code to change the target area to UpperCase. This works fine.
If Not Intersect(Target, Columns(2)) Is Nothing Then
Set rng1 = Intersect(Target, Columns(2))
Set rng2 = Intersect(ActiveSheet.UsedRange, rng1)
For Each cell In rng2
If cell.Formula "" Then
cell.Formula = Format(cell.Formula, ">")
End If
Next cell
End If
I could not find anything telling me what the ">" means. I'm assuming that it is a special symbol/wildcard for UCase in VBA.
My question(s), is there a symbol for ProperCase so I can use the same code, just making it change the Target column to Proper? Also is there a list of the special symbols.
Proper Case Exemptions
Jun 3, 2006When I asked this question before, I was looking for a way to automate the exemptions on a UserForm. At that time I realized that automation was not a good choice and went with a CommandButton to turn off the Proper case for that entry. I am now trying to do the same thing on a Worksheet change event using this
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column < 1 Or Target.Column > 9 Then Exit Sub
On Error Goto Errhndl
Application.EnableEvents = False
If Target.Column = 1 Then Target.Value = Application.Proper(Target.Value)
If Target.Column = 2 Then Target.Value = Application.Proper(Target.Value)
If Target.Column = 3 Then Target.Value = UCase(Target.Value)
If Target.Column = 4 And Target.Value > "" Then Target.Value = UCase(Left(Target.Value, 2)) & "-" & Right(Target.Value, 2)
If Target.Column = 8 Then Target.Value = UCase(Target.Value)
If Target.Column = 9 Then Target.Value = UCase(Target.Value)
Application.EnableEvents = True
Exit Sub
Errhndl:
Application.EnableEvents = True
End Sub
My problem is with Target.Column = 1. I need a way to disable the proper case for a single row. I tried to use an additional column (J) and place a x in that row, but I could not figure out how to detect if there was anything in that column for the target row.
Extract Proper Case Words
Aug 17, 2014I have been using following code to extract all upper case words in a string but the problem is I can not extract words which are proper. For example
This is GOOD
Present output: GOOD
Desired Output: This GOOD
[Code] ....
What can be suitable modification in this case?
Proper Case Mac, Mc Names In TextBox
Dec 10, 2008I have a number of textboxes into which I enter the surname of individuals ... at present the textboxes are set to store all names in Uppercase. Is there coding to return names beginning Mc... or Mac... ie McClOUD or MacDONALD, in the more recognized format. I am sure this has been included in the forum but could not find it in a search of the site.
View 4 Replies View RelatedProper Case On All Sheets Except Sheet1
Sep 19, 2006Is it possible to modify this code to exclude the first sheet in the workbook which is called Costs?
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
''''''''''''''''''''''''''''''''''''''''''''
'Forces text to Proper case for the range A14:A39
''''''''''''''''''''''''''''''''''''''''''''
If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
On Error Resume Next
If Not Intersect(Target, Range("A14:A39")) Is Nothing Then
Application.EnableEvents = False
Target = StrConv(Target, vbProperCase)
Application.EnableEvents = True
End If
On Error Goto 0
End Sub
''''''''''''''''''''''''''''''''''''''''''''
'Forces text to Proper case for the range A14:A39
''''''''''''''''''''''''''''''''''''''''''''
Refine Proper Case Macro
Jan 25, 2007I am using this macro to ensure that a range of cells appear in Proper Case. However I am encountering a drawback, sometimes I have text which I want in Upper Case but which is changed into Proper Case. I was wondering if there was a way to work around this. Example: Practical W/W appears as W/w or Woodturing (GMC) appears as Woodtrunign(gmc)
Private Sub Worksheet_Change(ByVal Target As Range)
''''''''''''''''''''''''''''''''''''''''''''
'Forces text to Proper case for the range A15:A40
''''''''''''''''''''''''''''''''''''''''''''
If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
On Error Resume Next
If Not Intersect(Target, Range("A15:A40")) Is Nothing Then
Application.EnableEvents = False
Target = StrConv(Target, vbProperCase)
Application.EnableEvents = True
End If
On Error Goto 0
End Sub
Proper Case Text By Condition
Oct 19, 2007How can I extend proper() to NOT change "PO Box 333" to "Po Box 333". Ideally, I would like to supply a list of words such as PO and all the 2 letter directionals (NE,NW,SE,SW).
There are also cases such as a last name of MacNamara which should have a capital M and N. Even worse, I see that 3rd becomes 3Rd which is very sad.
I'm assuming the data was supplied in uppercase
Change Case Of Text To Proper Except For Prepositions
Oct 17, 2012macro that will change the case of a string of texts to proper except for prepositions (e.g. and, or, about, the, etc.).
View 2 Replies View RelatedPrevent Proper Case Of Letters Following A Comma
Mar 17, 2007When using the PROPER function, it capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter, and converts all other letters to lowercase.
However, if A1 contains the text "2-cent's worth"; then =PROPER(A1) will return the following result: "2-Cent'S Worth".
Is there a way to prevent the PROPER function from capitalizing the first letter following the apostrophe?
Force Upper Or Proper Case On Entry
Feb 16, 2007Below is the existing code that I'm working with and would like to be able to make the ' name' column either Upper or Proper case on entry. I haven't decided which I'm going to use yet.
Set r = Sheet1.Range("A2:C65536")
If Not Intersect(Target, r) Is Nothing Then
sTgt = Trim(Target.Value)
If sTgt = "" Then Exit Sub
Select Case Target.Column
Case NmCol
If InStr(sTgt, ",") = 0 Then
iSpc = InStrRev(sTgt, " ")
Target.Value = Mid(sTgt, iSpc + 1) & ", " & Left(sTgt, iSpc - 1)
End If
Make Text Proper / Sentence Case In VBA Module
Jul 29, 2003Some handy code that I can put in a VBA module that will convert all text within a Spreadsheet to Proper or Sentence like this ---> Hello Everyone, Hope You Are All Happy.
View 7 Replies View RelatedFormatting Individual Columns To Proper And Upper Case
Mar 13, 2014I have a worksheet which is populated from a macro using the following code.
Code:
Sheets("Create Sub Contractor").Range("B6:B65").Copy
With Sheets("Sub Contractor Information").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
.PasteSpecial (xlValues), Transpose:=True
End With
The problem is that some of what is being copied needs to be Proper and Some Upper, therefore I cant use a paste special option.
What I'd like to be able to do is format the columns in the destination sheet ("Sub Contractor Information") from row 4 down to what ever format they need to be individually as there are some columns that are numbers, some text and numbers........
Change Text To Upper, Lower Or Proper Case On Entry
Sep 6, 2007I would like to format a row of cells so that when a word is entered into the cell it automatically becomes a capital.
I need the word to be capitalized so that I can use it in a custom function. The function uses the word from this cell and goes through a bunch of cases in determing how to classify the string.
I think more than one solution is possible and I would greatly appreciate some feed back, I've tried looking into turning all the letters of a string in my VBA code to capitals, or a way to format the cells, so that the string is already capitalized when entered into the VBA code, but I'm still a novice at VBA and unsure on how certain commands work.
here is a sample of my vba code.
Function WeightI(Shape As String, sDim As String, dLenFt As Double) As Double
Const pi As Double = 3.14159265358979
Const Ft2In As Double = 12
Const dDen As Double = 0.2835 ' density of steel, pounds per cubic inch
Dim aiStr() As String ' dimensions as strings
Automatically Change To Proper Case Once The User Leaves Any Of The Referenced Cells
Nov 6, 2008In my worksheet there are ranges A3:C37, E3:E37, J3:K37 and P3:P37 that all contain text that I would like to automatically change to proper case once the user leaves any of the referenced cells.
I have tried various codes form this forum and searched for hours on the net for a solution to do this but no matter what I do/try nothing works (for long)
Another forum user did help me out with some code but there was an issue with column C, L & O (which are set as drop down lists) and when the code was put into the workbook these columns stopped working and froze the app.
Converting Numeric Data To Proper Dates
Jan 4, 2014I need to convert numeric data to proper dates. Example: a cell currently reads 100875 but I need it to display 10/08/1975.
I've already found a VBA script that properly formats new data as you enter it (keying 1298 results in 1/2/1998), and I'm familiar with using =DATE(left,mid,right) to coerce Excel into spitting out a date in a certain format.
The difficulty I'm having is that I need to make existing data display correctly, without adding another column to accommodate reinterpretation of said existing data through a formula. Essentially I'm looking to avoid having to re-key several thousand date entries.
Converting Text From Upper Case Or Lowercase
Aug 8, 2008I have created a text convertor which will convert any text into upper case or lowercase (not very complex but very handy!) Is there anyway way that when text is in uppercase and I convert it to lower case that it makes every character after a full stop in upper case
Example
DAVID WAS NOT WELL. HE WENT HOME
to
David was not well. He went home
I currently get
david was not well. he went home.
VB Macro For Proper Case To Ignore "/" & "-"
Oct 7, 2008I have a Excel text document that I compile from multiple sources, and if they don't leave a "white space" before a "/", "-", or "(" the Proper Case command does not capitalize the word that immediately follows that symbol.
View 4 Replies View RelatedNested If Or Select Case Statement To Handle A Worksheet Event
Jan 31, 2010I would like to have a nested if or select case statement to handle a worksheet event.
The conditions it will check are:
1.Make sure target is w/in range, otherwise EXIT
2.Make sure that target offset value is not empty, otherwise display message
3.All is good, open form
I’ve tried various formulations and positionings of the statements ,but not all conditions are met with equal success.
What happens is I get the the first two conditions, but the third doesn’t work.
Add Characters Between Lower Case And Upper Case Letters
Aug 26, 2009I have a string of names that run together without spaces or commas between each name.
"Danny TrejoJean Claude van DammeVincent SchiavelliGabrielle FitzpatrickDavid 'Shark' FralickPat Morita" for example.
Is there a way to add a comma and space between a lower case and upper case letter?
Converting Worksheet Formula To VBA Code
Jul 6, 2006I'm having a slight problem as I cannot find a way to translate the following code from my worksheet formula to VBA: =IF(LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))=1,A1,MID(A1,1,FIND("-",A1,FIND("-",A1)+1)-1)). The code takes a value from one cell and takes the part that I need. There are two main types of inputs, they are in the form:
aaa-aaaaaaa-aaaaaa
aaaa-aaaaaa
The lengths are variable, but for the sake of showing you an example I have simplified it to the previous. The formula takes the part of the string that is before and after the first hyphen and puts it in the cell next to it.
Converting A String To A Worksheet Variable
Feb 1, 2007I have a userform that reads in a list of names into a combobox. When you choose a name from the combobox it then shows the that persons details from a sheet containing all the names.
I have some text boxes that allows the data to be modified and returns it to the sheet which works fine. What i also want it to do it put the data from the form into a sheet for that individual. For example the name of Ford would go into the Ford worksheet.
How do i read in the value from the combobox on the worksheet and use it as a variable to use in my code. For example
Sheets(Value from the combobox).Cells(NoOfRows, 3).Value = Me.TextBoxChangePosistion.Value
*NoOfROws is the variable to find a blank row
Number Formatting Not Converting Into Connection Worksheet?
Jun 3, 2014I have an excel document saved in a SharePoint document library. I then have another excel document where I created a connection back to the document being saved on SP (went to data connections->add->browse for more->enter the SP URL & found the document) and then opened that connection up as its own worksheet (existing connections->chose my connection). In the original worksheet (saved on SP), there are about 20 columns that are formatted as numbers. However, when I open the connection up in the new document, half of these columns are still number formatted, but the rest are being displayed as text EVEN THOUGH they are technically formatted as numbers (I right click->format cells & they are formatted as numbers). I can click into a cell and press enter and it will "come up to speed" and enter into number formatting, but as soon as I refresh my connection back to the original document, they go back to their text formatting despite the fact that the connection they are drawing from has them saved as numbers! Very frustrating. Its not a SP issue because I recreated the scenario by saving the original document to my computer and it did the same thing. I even went through one column in the original document and made sure there were no spaces saved to trigger the column to go to text- nothing.
View 2 Replies View RelatedSelect Case, Case Else Copy From Above Cell
Jun 3, 2009I've got a pretty intense macro already written, a lot of Select Case components. At the end, if nothing matches I'd like to just copy the cell above to the cell below. However, there is a range of about 400 cells in length, so I'd need some sort of wildcard for range.
Rows("2:2").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Dim Cell As Variant
For Each Cell In Range("A1:OL1")
Select Case Cell.Value
Case "Eng1"
Cell.Offset(1, 0).Value = "Engine One"
tons more in the middle here
Case Else
Cell.Offset(1, 0).Value = "N/A"
Rather then returning "N/A", how could I reference the cell above and just copy it instead?
Converting 2 Columns With Repeating Info Into Another Worksheet In Horizontal Format
Jun 28, 2013taking a spreadsheet that has vertical repeating info in Column A and results in Column B and converting that to another sheet in a horizontal list. The main problem is that the repeating info in Column A may or may not always be the same for every customer; therefore, when it is placed in a horizontal format some cells may or may not have results. I tried a arbitrary lookup. Here is the formula I used:
{=INDEX(INFO!$A$1:$B$300, SMALL(IF($A$1=INFO!$A$1:$A$300, ROW(INFO!$A$1:$A$300)-MIN(ROW(INFO!$A$1:$A$300))+1, ""), ROW(B1)),COLUMN(B1))}
That got me started. I am willing to use a macro. Here is some sample data:
Table 1 has a sample of the info:
IDENTIFIER
CUSTOMER INFO
2000
111111
[Code].....
Case Statment Not Stopping At A Case
Apr 22, 2009I decided to try to change it into a Case Statement. Here is what I have now. But the problem seems to be this time at this line: When I have "01" in C5 the script just keeps going?
View 14 Replies View Related