Excel 2010 :: Text To Column Runs Automatically?
Dec 15, 2012
I'm using Excel 2010 and have run into the following problem. I have data in a text editor that I need to separate. I copy and paste this data into Cell A1, then do a "Text to Col" function that separates the data correctly. Now I have other data to paste into other cells on the same sheet. Here's the problem....The "Text to Col" function runs automatically when I paste other data into any other cell on the WS. How do I turn this off without closing the WS?
View 4 Replies
ADVERTISEMENT
Jun 20, 2014
On my work computer (Excel 2010, Win 7) when I am stepping through my VBA code using F8 and come to a Workbooks.Open line, the macro automatically kicks in and runs through to the end instead of just stepping to the next line of code. This happens nearly every time but occasionally it works as it should. To prevent this, I have to put a breakpoint on the next line following Workbooks.Open and after that I can step (F8) through the rest of the code. I have never had this problem before and it doesn't happen on my home computer.
View 4 Replies
View Related
Jul 24, 2014
Basically I first want to be able define around 100 variables such as:
Dog = 1
Cat = 2
Mouse = 3
And so on. The reason being my final data needs to be the numbers for it's purpose but because there are so many I can't possibly remember them all to just type out 23 | 44 | 76 | 2 etc. What I would like to then happen is that whenever I type out the words in a cell it's automatically replaced with the number ID.
So typing in
Dog
Mouse
Mouse
Cat
Dog
would automatically result in
1
3
3
2
1
In those cells when I hit enter or at the end in one go. Is this at all possible in Excel 2010?
View 1 Replies
View Related
Mar 5, 2014
I am trying to count the distinct times a value shows up in a column, if another column has a 2 in it.
For example:
Columns
A, B
2,P25
2,P25
3,P5
3,P6
2,P5
The results shoud be: (2) Because I only want to count the P25 one time.
I'm using Excel 2010
View 2 Replies
View Related
Jul 22, 2014
I have a column of numbers that are already formatted as text. Some of the data is duplicate which is fine. It's like a list of 6000 numbers. I want to conditionally format the column so that I can fill the cell with a color as long as it stays the same number, but if it changes to a new number make it a different color. I don't need a lot of colors. Two is fine. TI have Excel 2010.
View 4 Replies
View Related
Mar 10, 2014
I'm using Excel 2010). I have column I, which is the sum of columns A and B (first and last names of patrons), and I have used conditional formatting to identify all duplicates in column I with red text. I would now like a macro to find red text in column I and then delete the corresponding rows.
I've been looking around on the web for a while and have cobbled together this code stolen from other people's macros:
[Code] ....
When I run it, I get the following error:
Run-time error '1004':Application-defined or object-defined error
Excel doc is called "stupid mailing list.xlsm", sheet I want to run the macro on is called "voters".
View 1 Replies
View Related
Apr 16, 2014
Excel VBA 2010.
I have this problem:
e.g Cells ""
C2 - 128.50 g
C3 - 211.01 g
C4 - 198.50 g
C5 - 179.34 g
I need to split the text into
Cell "C2" = 128.50 and Cell "D2" = g
Cell "C3" = 211.01 and Cell "D2" = g
and so forth
I wanted to avoid using delimited method with space and induce an automated method to format every cell of Column "C" one by one.
View 6 Replies
View Related
Feb 24, 2011
I am using Excel 2010 and I have a password protected workbook with password protected sheets that uses several macros. Most of them, in order to run, have to un-protect the sheet and then re-protect it again. This has been accomplished easily enough by adding ActiveSheet.Unprotect Password:= "mypassword" and ActiveSheet.Protect Password:= "mypassword" to the appropriate places in the script. All of my macros, which do various things like sorting and moving data, deleting blank rows, displaying dialog boxes containing warning messages etc. run fine.
My problem is this: when I password protect the sheets manually, I have checked the following options in the "Protect Sheet" dialog box. Under "Allow users of this worksheet to" I have checked 1)Select unlocked cells and 2)Format cells. After entering my password and closing the dialog box my sheet is protected, but I can edit cells in the manner my allowances permit. However, once I run any of the macros that un-protect and re-protect the sheet, I remain able to select and edit unlocked cells (practically, for my purposes, this means that I can input data which will appear in the default font size and color of the sheet) but I cannot format cells (which, practically, for my purposes would allow me to occasionally change the font color and size of the data). Naturally, after running a macro, the other cell-formatting options are unavailable to me as well. Is there any way to get my manual selections to remain in place after running a macro that functions as mine do? Or is there any way to make my manual selections the default settings for a protected sheet?
View 4 Replies
View Related
Jul 11, 2012
Software: Excel 2010, Windows 7
What is the VBA code for inserting text in all column B-cells of multiple selected rows?
I am creating a worksheet with a table containing various data related to orders of various materials (this is more or less data gathering from an older, paper-based 'system'). This table spans, columnwise, from A to D and expands downwards as more orders are added. The information in each column is: A=order number, B=type of material and C=material specs. and D=additional comments.
I've set it up so that the only thing I really have to do is to insert the type of material in the cells of column B, and the rest will sort itself out. Instead of having to insert a new row for each new entry and manually typing in the name of the material (these entries are often done in the midst of already existing data), I created several similar, macroed buttons for the different types of materials we use. These macros work by selecting the row of the currently active cell, inserting a new row and then add the name of the material in the column B-cell of this new row. What I am having trouble doing though, is to get the text-entry to work for a selection of multiple cells.
As an example, lets say that I would like to add 5 orders of "Grade A Steel" in the middle of the table - in the row above row 8. With the macro I currently have I can select cell B5, click the macro, and a new row will be inserted with "Grade A Steel" in column B of this new row. This action could be performed 5 times over, but would be easier if I could just mark a range of 5 cells, say B8:B12, click the macro and get the text/data inserted the column B-cells of all 5 of the new rows. So far I've been able to create a macro that inserts multiple new rows, but I've only been successful in filling the column B-cell in the first row leaving the 4 below empty.
View 8 Replies
View Related
Jul 31, 2014
Just to see how calculations were working out, I was trying to get a macro to scroll down the window as it calculated data.
Sadly, ActiveWindow.SmallScroll Down:=2 was too fast and =1 was too slow!
That's the only command I know to get this to work.
demonstration of a different command. Any way to get the window to scroll automatically to the cell which has just had information inserted into it?
Here's the VBA:
Sub CustomerCategory()
Dim Rng As Range
Dim Dn As Range
[Code]....
View 2 Replies
View Related
Dec 11, 2013
I have 5 worksheets that I currently have to add information to and cut/paste information from one to another. The initial information stays the same but I have to cut and paste it into one of the other worksheets based on wether or not we; need to decide on a job, are working on the job, lost the job, won the job or the job is complete.
I would like to create one master worksheet where the information can be entered with a drop down cell stating the status of the job (listed above). I created the master tab and linked the 5 subordinate tabs using an IF formula, but how can the subordinate tabs filter or sort the references and organize them on the top of the page rather than leaving a bunch of blank rows (because the info in these blank rows went to a different subordinate tab)? I want to enter the info in the master tab and simply change the pull down cell to change which subordinate tab the information shows up on. This should allow me to print the subordinate tabs as reports without having to manually cut and paste the info or filter it, correct?
View 5 Replies
View Related
May 2, 2012
I am attempting my first working macro. I am currently using Excel 2010 on Windows XP. Here is what I am attempting: I need a very user friendly macro that will allow my staff to click on a button to run the macro. Each month they receive multiple workbooks from one of our clients. The workbooks are always stored in the same location. I need the macro to go in and pick up the first worksheet of each workbook and copy it into a new workbook. Each original sheet1 of each workbook should have its own sheet in the new workbook, i.e. if there are 5 original workbooks then the macro should create one workbook with 5 sheets. Here is the programming I have been using:
Sub GetSheets()
Path = "C:Documents and Settingse462863My Documents
utterblotter"
Filename = Dir(Path & "*.xls")
Do While Filename ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=ThisWorkbook.Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub
This works, but I am running into a few problems. I need to create a "run" button but I'm unsure how to build it. Also, when I run this macro there are tons of extra sheets and the worksheets tend to duplicate. I am wondering if the "loop" is picking up more than I need.
View 9 Replies
View Related
Apr 18, 2014
I'm relatively new to Excel and I'm currently making a basic spreadsheet for my personal income/spending.
How would I make the "Earned this Month" and "Spent this Month" tabs in the top update on a month to month basis automatically?
For example, in the month of may, it would display may's values, june would be june's etc. etc.
(Excel 2010 w/ Windows 7)
View 3 Replies
View Related
Apr 26, 2012
I work for an insulation company and we have all of our jobs, completed and in progress, on a master worksheet.
Currently there are 437 rows of data (but will increase), and columns A to N with various bits of data.
Row A is a location field - there are 5 locations currently.
I would like to be able to add a new line at the bottom of the master sheet, and then this automatically identifies the location from column A and which worksheet is it to be copied to and then copies the data from that new row to the bottom of the relevant location sheet.
I would also like to be able to update the data in the existing entries (e.g. when a job has been assessed initially, and then completed, I need to put the dates in) and for this to update on the relevant worksheet.
Each worksheet has the same format (columns A to N have the same headings in row 1, then data to begin in row 2).
I wouldn't say I'm an advanced Excel user (otherwise I wouldn't be asking this question), but I do have a fairly good working knowledge of it. Currently using Excel 2010. Would ideally like to be able to do it without VBA as it needs to be uploaded to Google Docs and for others in the company to access online.
View 9 Replies
View Related
Jan 24, 2012
what is the proper terminology for the Outlook "Thing" that opens up in excel (shown below)? Is it simply a toolbar? Add-in?
Second question, I have a excel file that automatically loads the outlook "Toolbar" each time it is opened.
The file was made in 2010 and password protected and locked, sent to a worker who edited the appropriate fields and saved, and then they sent the file back to me (2003 Format). Now this file automatically loads the outlook toolbar. I am trying to trouble shoot so what would cause this behavior?
View 4 Replies
View Related
Mar 19, 2012
1- I dont know why but sometimes excel 2010 converts numbers to dates automatically in my workbook. I use this macro to solve the problem, but since last week it is not working. I get an error: "Method 'NumberFormat' of object 'Range' failed".
2- because it should work in all cells of the workbook, the macro must make changes only in the cells with absolute numbers (ex: 1223) but must not change if in the cell there is a date like 12-may-2012 (numbers with hyphens).
This is my function:
_____________________________
Sub PuxaDatas()
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Sheets
[Code].....
View 1 Replies
View Related
Aug 27, 2013
I use 2010 and I will try and I need to complete a relatively simple project that I am working on.
I have a range of cells, "D7:Q30". This range has numerical values that are updated automatically (every cell within the range has a numerical value) from an "old value" to a "new value". The refresh rate is not constant, that is the values may change at any time depending on when a value changes I think it is called "pushed" data. Once a value is updated ("new value"), it stays at that value until one of the updates changes its value (and then it becomes the "old value").
I would like a message box to display the following EACH TIME A CELL CHANGES ITS VALUE when the NEW updated value is LESS THAN the OLD value:
"B51, B52, **5, has a changed value from "old value" to "new value".
The ** above in the message box represents the following: If for example cell F23 changes, ** in the message box should be the value that is in cell F5. If Q10 changes, the ** value in the message box should be Q5. It will always be the value in Row5 but have a different column depending on where the change is detected.
B51 is the text value in B51..same for B52 (text) etc...these values change depending on what spreadsheet I have open, but will always be in those cell positions. But the values within the range D7:Q30 are always numeric.
View 9 Replies
View Related
Jun 14, 2014
We have a schedule that creates an Excel file which separates each order by blank rows. I need each order to be separated by a page break, so each order prints on a separate sheet.
Each order entry is 1 or 2 rows
Using Excel 2010/2013 although the file produced is an xls file.
Columns A to J are populated
Rows 1 & 2 are the header
I have tried a macro that involves me doing a countif and counting entries in the row, and if they add to 10 it inserts a page break, but its bit long winded and only seemed to work once.
View 3 Replies
View Related
Nov 16, 2012
i have a question with regards to the shading of a table in excel 2010.The grey/white shading of the sheet should adjust automatically when lines are inserted / deleted...what would be the most efficient way?
View 6 Replies
View Related
Mar 2, 2012
I have a peice of code that i know is inefficient and it is in danger of becoming too large. I have a spreadsheet that has circles aligned to each cell. There are around 100 in total. The code changes the colour of the shape based on the cell value in which it sits. However, the code needs changing and also it does not automatically update the colour shape even though the cell value changes. I have to manually select a cell and then the formula bar and then press return for it to update.
I am using excel 2010.
This is the code i am using for each shape.
Code:
If Range("n12").Value = text Then
ActiveSheet.Shapes("Oval 250").Fill.ForeColor.RGB = RGB(255, 255, 255)
End If
[Code]....
View 6 Replies
View Related
Nov 6, 2012
I am a Microsoft Excel 2010 user and am trying to create a usage report for a website that I work on. I have been compiling the usage reports into one spreadsheet. The columns I use are Month, Device, Language, Title 1, and Title 2. I can filter each of these rows and it shows me the number of records found. Each row on this spread sheet refers to a time that someone selected something on the site. For example the row may say;
Month (filtered)
Device (Filtered)
Lan (filtered)
[Code].....
How do I automatically populate a table on a separate sheet with the number of records found for a certain combination of filters applied. So if I'm looking at monthly trending I want to know how many times the stress test was started in May, June, or July, but automatically using info from the multiple filters.
What is a formula I can use to compile the filtered data from above.
Jan
Feb
Mar
Aprl
May
Start
=formula?
View 4 Replies
View Related
Mar 18, 2014
I am my excel worksheet (excel 2010) I have one cell that changes every day (number). I want this number to open my htm document and replace the same number in a string in the htm and save/close this.
An example:
My htm document is located at C:/ and named XX.htm
The number I want from excel is in cell A1 in sheet1, and the worksheet is located in D:/ named yy.xlsx
And the text(number) I want to replace is in the following string in the htm document, in this string it is 72, next day it can be 30:
src="Bilder/72.png"
View 8 Replies
View Related
Sep 13, 2013
I need to put a number as a text. It is 14 digits long (35071245130000). If I change it from a number to a text, it re-formats to a scientific number (3.50712E+6).
I can not use the ' in front of the number because it makes all of my vlookups mess up.
The reason I need this changed is because I have a several databases/excel spreadsheets that are being linked through Access. The number is stored as text in the other databases. Because of this, the spreadsheet with the number is not recognizing the number version and the text version as the same.
How to make excel show this full number when formatted as text?
I have Excel 2010. I do not remember having this problem with other versions at my previous employer.
View 2 Replies
View Related
Nov 3, 2013
I am working on Excel 2010. I want to find a way to link data from one spreadsheet to another one and whenever I update the first spreadsheet, the second one will be automatically updated?
View 2 Replies
View Related
Jul 2, 2014
I am making an order for my shop and I want to multiply product price X (Column #1) with quantity Y (Column #2) and sum it.
I have a picture attached : uznSuuc.png
I want it to be A2*B2+A3*B3+A4*B4+A5*B5+A6*B6+A7*B7=Z (Total)
BTW My office version is 2010.
View 12 Replies
View Related
Sep 16, 2013
I am using Excel 2010. I have been given a task at work that can save my team a lot of time if I can solve the problem. Every month, we have a spreadsheet with about 5000 rows that we have to email. In each row, there is a range that we have to email to a specific email. For example, I would have to copy and paste Range A2-R2 in the body of the email, and then email it to whatever email is in cell S2. I would then continue this for the next 5000 lines, making it a possibility that i will be sending 5000 emails manually.
I have been trying to come up with a solution through VBA that would automatically send these emails. My goal is to automatically send the Range A2-R2 to outlook email, then cell S2 into the "To" email address box, and then automatically send it. So far, i have successfully been able to send one row, but cannot figure out how to loop it for the remainder of the rows.
A couple other key points are that I have column headings as well (Range A1-R1). If possible, I want to be able to include the column headings in the email body as well. Example - first email would be range A1-R2. second email would be range A1-R1 and A3-R3, and so forth. The body of the email would also contain a standard script, such as "Please review the information below."
The goal here is to save everyone from having to send 5000+ manual emails. This would be a big boost for my team.
Code:
Sub Email()
Dim rng As Range
Dim OutApp As Object
[Code].....
View 5 Replies
View Related
Jan 13, 2013
I have this text at J22 and i would like to break out 13 and 95 000 out of this:
<td><span>13</span></td><td class=""right_align""><span>0-0-2</span></td><td class=""right_align""><span>95 000
The numbers and lenght of the numbers where 13 and 95 000 are may vary in lenght. I need them to parse the values at those spots to separate cells.
Im using excel 2010
View 12 Replies
View Related
Mar 11, 2014
When I combine 3 reports (which we get out of a system) into 1 big file, the date format remains the same (mm/dd/yyyy and right alligned). Same happens for most of my colleagues.
When 1 particular colleague goes and combine these reports, I've noticed that some of the dates are showing as text? (dd/mm/yyyy and left alligned).
View 3 Replies
View Related
Mar 29, 2014
I'm in a text box in Excel 2010. I need to find the current cursor position in the line of text in the box - using VBA.
View 2 Replies
View Related
Dec 6, 2011
I received one excel spreadsheet office 2010 every week from one on the vendor. it has over 55000 rows. I got columns N, O, P & Q. these columns has some value which I use for my pivot tables. However the values in this columns are stores as a text. I select the little small box (information box ) appears on the right side of the cell and right click on that where I get an option to change it to number. This is working well.
However I have over 55000 rows , it takes a lot of times to change all these cells in four columns ( almost over 220,000 cells). some times more than an hours.
I use excel 2010.
View 3 Replies
View Related
