In one column J there are five digit numbers like 45678.Can with the help of a formula or code/macro, the process of text to columns can be done in one click, instead of going through the process available so that the data is scattered in J,k,l,m,n columns.
View 3 RepliesI have columns A to N hidden and need to reveal them. I know you can manually drag each one to reveal it. But I thought there was a way through highlighting/menu options to reveal them all instantly.
View 3 Replies View RelatedI know it is possible to run a macro when clicking a specific cell in a worksheet. Is is possible to run a macro when click ANY cell in a specific column.
View 2 Replies View RelatedI want to be able to have a set of buttons or a dropdown menu where i can put all the titles of my columns in.
After clicking on one of the titles the column must hide or show itself, is there a way to do this?
It's for an automated sheet wich contains a lot of information and this will be used by a lot of people, i want to make it easy for people to simply generate a sheet with just the info they need.
I have data of all my retail stores around (550)(Store Names in columns) with Product description and Sale & current Inventory, it seems very tedious job to add all the 550 stores in data area so to get sale and stock value, is there any way that I can add all stores in data area in one go.
View 3 Replies View RelatedHow to run some VBA code only when double click any cell of three different columns (say A, G,H) and not run in any other cell. This is being run in Excel 2007.
View 7 Replies View RelatedI have a Cell with 3 text boxes spaced horizontally. My boss wants me to figure out a way for when I click each of those text boxes an image appears beside the textbox.
So if i click the first textbox a check appears beside the first textbox. If I click the 3rd textbox then beside the 3rd etc.. Also, In the cell beside it he wants a number to appear when you click the textbox. So say the first textbox is clicked the number "1" appears, the 2nd one is click the number "3" and the 3rd one is clicked the number "6" (for example).
I'm building a userform (for the first time), and I'm working with a textbox control. I'd like to make it so that when the user clicks in that textbox, the contents is selected, so that if they begin typing, it will type over what is already there.
My question, specifically, is whether or not there is a property I can set to make this the default behavior, and if not - what event do I tie to this action? Is it the "Enter" event?
What I have now, which will probably be unacceptable to my users, is code that looks like this:
Private Sub tbName_Enter()
ActiveControl.Value = ""
End Sub
This makes the contents disappear, but I'd rather they stay there and just become "selected".
I am using Excel 2003 on Windows XP, and I have created a form using several TextBox controls on Worksheets (not userforms). When I click on a textbox that has some text in it, the size of the text grows. Clicking away from it, and then clicking on it again causes the text to grow further. Repeating these actions ultimately causes the text to be so large that the contents are illegible. There is no VBA code manipulating the font in the controls, they are only set initially through the settings in their properties (all textboxes use the same font). The problem occurs on multi-line as well as single-line textboxes. In debug mode, I can see that the font.size has not changed from the initial setting of 9, so there is no manipulation of the settings. It looks like the control is being zoomed, as the size of the vertical scrollbar grows along with the text. Closing the file and reopening it doesn't resolve the problem. The textbox retains the size that it has grown to. This problem seems to happen on some environments and not others, and I have not been able to determine the cause or factors that contribute to this phenomenon.
View 5 Replies View RelatedI wonder if it is possible to make a macro that right clicks a cell and then chooses a option from the list?
View 11 Replies View RelatedI have a macro which imports data from a mainframe dump text file and performs 'Text to Columns' on the imported data so that formula in the spreadsheet can act on the data. The code works perfectly well when I use it, but if a different user logs on and performs exactly the same mainframe dump and import macro the Text to Columns action splits the raw data in a different way and the result is that the split renders the formulae useless.
I've experimented a little and for some reason it appears that the 'Field Info' parameters which are produced when the Text to Columns function is recorded in a macro differ between users even though the raw data is exactly the same.
FieldInfo:= _
Array(Array(0, 1), Array(18, 1), Array(35, 1), Array(56, 1), Array(70, 1), Array(88, 1), _
Array(102, 1))
how to set one entire columns text to two different colors based on another columns values. So for example I have column A and B. Column A has two values called Internal and External. Column B is a title table so the entire column is just titles. We'll say it goes for 20 rows if you need a row count. What I am looking to do change the text in Column B to Red for External and Blue for Internal. I tried the conditional formatting and I just can't seem to find the right option.
I'm using Win 8.1, Office 2013.
I've got some time values in an Excel Sheet in the format hh:mm:ss. I need to split them into columns (including the colon) like below:
hh: | mm: | ss
I can do this manually using text to columns but when I use text to columns in my macro, it automatically changes the time format to h:mm:ss PM
how to Chk the text string in particular cell, compare it with a super set column and get the full from of the text string from another corresponsing column and the output will be corresponsing full form of the chked text string?
View 6 Replies View RelatedLocking text in cells but not the ability to change colour of cells
******** width="234" height="60" frameborder="0" marginwidth="0"
marginheight="0" vspace="0" hspace="0" allowtransparency="true" scrolling="no" id="aswift_0" name="aswift_0" style="left: 0px; position: absolute; top: 0px;">*********>
I have a spreadsheet where I can change the colour of a cell by clicking the mouse, I also have text in many of the cells.
What I need to do is protect (lock) the text so that no one can change the text in any of the cells, but I still want to be able to change the colour of the cells by clicking the mouse in that cell.
I have the cell data as below
How would I split into a new column the first part which is a date into a new column, then the country and the remainder into separate columns?
I still want the original data as I need to check that the splits worked well?
16.5.90 CH 1671/90-4
18.10.1991 CH 3056/91-1
24.07.92 ch 2341/92-2
30.7.92 ch 2395/92-3
18.11.92 Us 3533/92-5
26.5.93PCT 1577/93-0
9.8.93 CH 2363/93-8
17.8.93 CH 2445/93-0
25.1.94ch209/94-6;8.12.94ch3714/94-1
25.1.94 ch 209/94-6 ; 8.12.94 ch 3714/94-1
8.4.94 ch 1047/94-0
22.4.94 ch 1255/94-7
18.11.1992 CH 3533/92-5
18.11.1992CH 3533/92-5
What I have is a column of data(text) which contains amongst all the text three strings of text in ever cell in the column which I require copying into three adjoining cells
The data I require is :-
(a) The persons name which is always after the word ‘Requester’ e.g. Requester Steve Robinson
(b) Their office location which is directly after the persons name and is in brackets e.g. (Newcastle User)
(c) The Approving persons name which is preceded by ‘Approved by’ e.g. Approved by Christine Hunting
See examples 1 & 2 below
Example 1
CR0/CRZ3651 Requestor Steve Robinson (Newcastle User) Tel: 01234 798157 Approved by Christine Hunting
Please install and configure 2 Ultra 2s (typhoon and lancaster) for use as ARTE workstations. These workstations require Solaris 2.5.1 plus the same patches as before
Example 2
CR0/CRZ3118 Requestor Doug Cunningham (Newport User) Tel: 0114 9881480 Approved by John Smithers
Please provide support to set up Cisco 2691 Router and PIX-506E Firewall to enable external connection of a remote terminal for project work.
As you will appreciate the text in the cells is of non standard lenght and the three pieces of information can be located virtually any where in the text
I am having a trouble in Excel sheet.My column A has a drop down list with text- possible, not possible, not required.Based on the text, i need to populate texts in columns B, C and D.
For example
Column A drop down selected is "possible"
then B coulmn should automatically populate "1-3"
C should populate with "3-5"
D should be "5-7"
I am using MS excel 2007.
I am trying to write a micro code to split text which is copied into cell A1 into columns. I can do this fine by going to "data" the "text to Columns" and selecting the places i want to split the text (this is the same for every piece of data i copy in).
The macro works perfectly every time. the problem is that the spreadsheet is shared and i want to protect certain cells on the sheet, when i protect the sheet the recorded macro does not work as the "data", "text to columns" is not available in a protected workbook.
I was just wondering if someone could help me, so i can run a macro to split the text which also allows me to protect cells. In the "text to column" option the "fixed width" (column breaks) i choose are: 4, 25, 34 and 43.
I have a cell that has a comma separated value that is 354 fields long. As such, if I use the Text To Columns feature to split the data at each column, I lose several columns (because excel cannot have that many columns).
How can I break the data at the comma, but have it list in rows instead?
In Column A1:A10 I have a really long series of alpha numberic digits in each cell.
I use this macro with text to column to split them up for me into different columns.
The problem I have is that after they go through this conversion all of the fractions in columns L are turned into dates....
how to add two columns of single words together, so that all possible word combinations are seen. For example:
Column1:
Horse
Pig
Dog
Sheep
Column2:
Run
Walk
Sit
Roll
So... I'd like Column3 to look like this:..........
The issue is I have about 100 words all together so there will be a lot of results! Is there a way I can enter a formula to do this?
I've had this issue a couple of times and can't work out an easy way to deal with it.
I have text data in one column.
Name
Add1
Add2
City
Pcode
Manager
Name
Add1
Add2
City
Pcode
Manager
etc
How do I extract Row 1 into Column 1, R2-C2,... R7-C1, R8-C2?
To make it more tricky what if there isn't a consistent amount of data, ie sometimes I'll have Manager name (6 rows of data) and sometimes I won't (5 rows of data) and then the next collection of data will have it again.
Does this make sense?
Running into this issue of "transworksheet macros"
I have a master "update" commandbutton on sheet 1. I have two sub "update" buttons on sheet 2 and 3 respectively.
What I would like to do is have the user click the master button, that would "click" the two sub buttons so they run their respective macros on their respective worksheets (within the same worksheet.
I have tried pasting the macro code in the master button, telling it to select the sub worksheet and then run the macro, but the marco runs itself on the master worksheet instead.
I have column "Due Date" which has data "Apr-09-2014 12:28:56 PM"
My code is to search Header "Due Date" and then doing a Text To column to get only the date on the entire column. I tried below code but something is not right.
[Code] ....
Is there a better way to separate one column into two, other than "Text To Columns" I am trying to separate a 'Chair Logic' from one comma or hyphen and don't want to separate it from the other. (ie. F335-2743,XH-4,S0-24 to F335-2743 | XH-4,S0-24) and (C-24-11-1T7-TRFJ-TRF-TRLE to C-24-11 | 1T7-TRFJ-TRF-TRLE).
View 11 Replies View RelatedI have a code that I need to add text at the end of the columns E and I.
The code is written so that if the col E got one more row that col I it will add text to the last row in col I.
But I also have to add text to each column after this, I need text1 in column E, and text2 in column I
Code:
Sub countrows()
Dim lrE As Long
Dim lrI As Long
With Sheets("Utvalg1")
[Code] .......
How I write the code for adding text at the end?
If I run text to columns from the data menu, it works just fine. Every cell becomes a date and is right justified.
However, if I record a macro, some cells fail to convert whilst others do.
My ....
I need to seperate text to columns. How can I have it look at a cell and only put text to column if it is seperated by more than 3 spaces. my data in cell A1 looks like this.
John Smith Sue Smith Frank J. Smith
Text to columns gives me 7 columns when I only need 3
I have to format huge text file to columns. Here is the file: [URL] The whole file should look like the first 15,16 rows. What is the fastest way that can be done? Here is the original file: [URL]
View 3 Replies View Related