I've noticed that when one pick up a value (word) from a cell in excel and try to use it in a form that it needs to be exactly the same (e.g. if a letter is capitalized in the cell, and not in the form, it wont be recognized as the same). So i was wondering if there is a command that one can type so that the form doesn not read the word for its capitalization but for the letter in it.
View 5 RepliesI am having problems with auto capitalization of words. I want to be able to enter words into a cell and have it auto capitalize in proper grammer ie: Name, the, or, and, 's. The problem I am having is that every word gets capitalized along with the 's.
View 2 Replies View RelatedI've recently created a macro which takes a .csv file and scrubs the data and dresses it up for a report. I've finally got it running without any problems and have added it individually to 5 different user accounts on my network as an add-in, but need a way to share it across a computer network without allowing others to edit the file.
The report is generated multiple times a day by the different users, none of whom are very computer literate (not that I am much better, myself), and the macro over-writes the old file each time. I've read that it's possible to simply create a shortcut to the file that automatically opens the spreadsheet in read-only format, which would be perfect for what I'm trying to do, but I haven't been able to get it to work so far.
My current shortcut pathway is similar to this:
"C:Program FilesMicrosoft OfficeOffice15EXCEL.EXE" /R "C:UsersBenDocumentsOT Master.xlsx"
On my computer at work (Windows 7) the shortcut simply opens the spreadsheet so that anyone can edit it, instead of as a read-only file. On my computer at home (I'm trying to experiment) (windows 8.1) it won't even let me save the shortcut, saying I need to provide administrator permission & then cutting to a window that says "access denied".
I won't have the pathway for the actual file I'm trying to create a shortcut for until tomorrow when I get back to work, but I've formatted it essentially the same, directing to the excel.exe file, running the switch "/R", and then directing it to the actual file location.
The ultimate goal is to have multiple people be able to view the file without locking out those who need to update it.
Is there a way to format a cell such that regardless of whether or not an upper case alpha character is entered, the character will appear in upper case?
View 9 Replies View Relatedwhen typing in a cell in excel, I sometimes need to put my initials in lower case at the end of a comment, indicating that I have responded in that cell. When I do, the first of my two initials changes to a capital instead of lower case. Can anyone tell me where to turn this feature off?
View 9 Replies View RelatedI have 40K asset list with different Capitalization dates, the depreciation will start either in the mid month, mid qtr or mid year (determined by dep key)
The goal is to come up with a accumulated depreciation up unitl 12/28/08.
The problem I`m running into is getting the depreciation start date (due to different years)
This is what I`m doing right now:
ie. asset cap date 12/5/2004
1. extract month: Dec
2. Extrac year: 2004
then I have table that says if the Month is Dec then the depreciation should be 12/15. However, when you enter 12/15 excel automatically adds in 2009. How do I replace the 2009 with 2004 ?
I'm making an order form and I want specific cells to ALWAYS display their contents in caps regardless of how text is entered, is there a way to do this? I looked all through formatting and could not find the option, I found the command for =Upper but that doesn't work if you put it in that same cell then type over it obviously.
is there a way to use an =upper command that'll make an entire block always print in caps?
I have a row of addresses - each was typed by users on website.
examples:
po box 1451 (would like to see: PO Box or P.O Box or P.O. Box)
PO box 427 (same here)
1735 park st. (would prefer 1735 Park St.)
I'm trying to open a file on a network drive...but I'm getting the following error message when it opens: "This file may be read-only, or you may be trying to access a read-only location. Or the server the document is stored on may not be responding." Now, the file itself has no rights restrictions and is not read only. It doesn't appear to be locked.
Now, there are other Excel files in the same directory which I could open fine; however, the Excel documents having the above problem all have a little black icon "appears to be a padlock" (image attached) at the bottom left hand side of the Excel file icon. I tried the following:
- Renaming
- Converting to a different file format (didn't work, it won't let me)
- Opening in notepad...etc doesn't work.
This file is dated back in 2004...do you think it's corrupt? Is there anything i can do to open or recover this?
I have a column of words in Column A and I want to replace all the times that these words appear in the rest of the excel sheet with the words in Column B. If someone has already answered a similar problem link me to the thread because I can't find anything.
View 5 Replies View RelatedI'm looking for a macro to remove all words (in a single word per cell format) in a range (approx 100 columns & 7000 rows), except for a list of 100 words.
I'd prefer to email the file if that's okay.
I've been using conventional method to do this and it's time consuming. I would like to total up 2 column. A multiply B to be exact. Below are some examples:
Table 1 - Before totaling up:
Quantity
Product
5
2 x Button A White
3
4 x Button B Pink
4
5 x Ribbon A Black
2
3 x Thread A White
6
2 x Cloth A Blue
Table 2 - After totaling up:
Quantity
Product
10
Button A White
12
Button B Pink
20
Ribbon A Black
6
Thread A White
12
Cloth A Blue
I need to have the sum of the "Quantity" multiply "Product". Or in short A x B.
And the end result need to have the number and "x" sign removed while keeping on the the products names. (2 x ) Take note it's "number" space "symbol" space.
I am trying to find certain words in a column and delete the word and characters following. For example, Say I have a column of info as seen below. This is a test of me. I am just experimenting with this stuff. Deleted (6/15/01) Let me know what you think. I am not sure about it all, but I guess I will figure it out. riviledge1 (01/05/06) Now let's see what happens when I try to test it.
I want to find all the "Priviledge1 (01/05/06)" and replace with nothing. Please note, the date will change with each record, so I need to figure out how to tell Excel to find "Priviledge1", delete it and the date behind it. So I want to delete "Priviledge1" and the next 11 characters including the space.
On to the topic, I have all the US and Canadian states abbreviated (CO = Colorado, etc.) and was wondering if there is a way to make all of them convert to their respective names in one fell swoop instead of writing it in for each one, one at a time. Something along the lines where I can make, CO = Colorado, TX = Texas and then hit enter and all of the abbreviations would convert. Some kind of command.
View 2 Replies View RelatedI want to sort on number of words, i.e. if a cell contains 1 or 2 words etc, with the cells containing 1 word coming first then cells containing 2 words. Also, if possible, first the cells with fewer characters.
View 9 Replies View RelatedI have tried Sumproduct and Countif and can not get a proper count of rows that contain specific words in multiple columns. I have attached an example spreadsheet where I am looking for a result of 7 rows that contain 1 to 4 specific words within phrases. The example looks for the words: "virus", "spyware", "malware" & "adaware", and ignores any other words like "aware" or "ware".
View 7 Replies View RelatedI have 4-5 separate lists of words/letters and I would like to combine them in a certain order but creating all possible combinations resulting a new word. It's like a conjugation tool for verbs. For example if List 1 = be/el/ki/meg; List 2= m List3=e/é; List4= gy/" "; List 5=" "/ek/sz/nnek So the output has to be (if the order is List1+List2+List3+List4+List5)= bemegy/bemegyek/bemegysz/bemesz/bemész/elmegy/elmennek/ ..... and all possible combinations.
Preferentially I would like to automate it in a way that it straight outputs into MS Access. Also can it be automated that it reads the list from a file (database)?
(Also can it recognize the word by letters? Because the basic word that I would load the program= List2+List3+List4)
Is it possible that when you press a command button, that the first thing it does is to execute the code assigned to another command button (IE in another sub).
View 9 Replies View RelatedI am playing around with the Forfiles command (being called from Excel via Shell)..
I can't for the life of me get it apply a second command (such as getting the file size of each file)..
For example:
VB:
Private Sub CommandButton1_Click()
Dim Z
Z = Split(CreateObject("wscript.shell").exec("cmd /c forfiles /P C:UsersapoDesktopTextFiles /S /M *.* /d
[Code]....
The end result being the filename and the filesize shown..
Excel 2007
I have this simple formula: =IF(SEARCH("ABC",BQ239,1)>0,"Found", "Not Found")
Instead of saying Not Found when the value is not in the cell BQ239 text string I get #VALUE! returned
how do I get it to say "Not Found"? I searching for a substring that can be anywhere in the string.
Is there any way to tell (from VBA) what GUI command is currently in progress or what the last command used was?
On a wish list perhaps: Application.LastCommand and/or Application.CurrentCommand
Let's say I'm in the "Sheet_Change" event and want to know what caused the change. Was the event triggered by "Delete", "Paste", "Keystrokes" ...
AutoCAD VBA has "Begin_Command (CommandName as String)" and "End_Command (CommandName as String)" events which I find very useful. Basically I'd like to emulate that to the greatest extent possible.
Application.Caller does not seem to work for the stated purpose. I've been searching for a workaround by means of reading the "Undo Stack". Numerous articles written by experts state that the undo stack is not accessible from VBA.
I've considered reading the text from the "Undo" button's caption but it just seems like such a hack ... not that this whole idea isn't a hack
i m wishing to make a ss available to all but only ready only format 2 people need access to ammend the data and spreadsheet.
View 2 Replies View RelatedI've created an add-in. When I try to update the coding in the add-in, Excel reports that the add-in is read only and prompts me to choose another name for it. However, it is not currently selected in my Tools -> Add-ins... window.
View 9 Replies View RelatedSimply stated:
Range("A5") = Qty
Range("A6") = Cost
Range("A7") = Cost * Qty
In the UserForm:
TextBox1 fills Qty range
TextBox2 fills Cost range
I'd like to have TextBox3 to show the result on Range("A7"). This box would not be enabled, therefore is not clickable or Tab accessible.
I tried
Private Sub TextBox3_Change()
TextBox3.Value = CStr(Range("A7").Value)
TextBox3.Text = Format(TextBox3.Text, "Currency")
End Sub
but that does not do it.
Lately I've been learning how to pluck data right from a web page:
for instance,
cageNum = IE.document.getElementById("txtCage").Value
will read the info right out of the txtCage textbox at the page where my IE object is.
Now I'd like to read a table into excel. How is that done?
I used to read *.txt file into C/C++ program?
Now from Yahoo downloading stock prices, they are in EXCEL *.csv format. How can read them into C/C++ program?
I've got an excel sheet from a guy at work who decided to lock the entire document down so I can't do anything with it. Virtually all the ribbon options are greyed out, there isn't anything locking it in the code, I've tried checking the save as "read-only" option and it's not there. How I can actually save this is editable? I've looked in windows explorer and it's not read-only in there either. I tried save-as and even tried making it an XLS instead of an XLSM and it still won't let me edit it
View 1 Replies View RelatedThe xls file I have written is used by multiple users at the same time. None of them can actually change the file - it opens data files and imports the data it needs then writes back any changes to the data to those files.
So the "read-only" option that my users see is of no importance to them - can this be bypassed somehow?
I have a text file that I want to read the data into a sheet and split and store into columns.
The format of the textfile as follows (Only two records will contain 200 with line break to seperate):
Atom "file" contains: C:B_200801142021_VIDEO_BOYS_EVENTS_FIRSTBIRTHDAY@Video Title 1.mp4
Atom "©nam" contains: Video Title 1
Atom "purd" contains: 200801142021
Atom "keyw" contains: VIDEO_BOYS_FIRSTBIRTHDAY
File "F_©nam" contains: Video Title 1
File "F_purd" contains: 200801142021
File "F_keyw" contains: VIDEO_BOYS_FIRSTBIRTHDAY
Raw data:
1 acct01 John Doe 10
2 acct01 Hits 20
2 acct01 Runs 05
Goal:
1 acct01 John Doe 10 20 05