I have a Column of Numbers that i need to change to this format 000-000-00, except i can't use the Format Cells option. Most of the number are eight long but some six and five long. For example:
30025141 needs to change to 300-251-41
109241 needs to change to 001-092-41
80493 needs to change to 000-804-93
The problems is i can't do it manually because there over 12000 numbers to change, so i need a vba code to do this.
Locking 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 a large worksheet that has one column that contains cells with either numbers, text or in some cases Nulls. Can anyone help me with some code that would loop through all the cells in this column and when it encounters either text (any text) or a null change the cell value to 0 (Zero).
View 10 Replies View RelatedI have 68 cells per sheet and approximately 70 sheets that i need to change to an array function.
Ive changed the formula used using Find and replace but now I need to convert ALL those cells to array.
Is there something I can use to change them all at once. Or am i going to have to go into each cell and F2 CTRL SHFT ENTER?
I have a dataset of shifts and want to compare each shift that needs to be filled to a list of requests for time off (vacation, etc).
For one cell the code is: =IF(ISERROR(SEARCH(B1, A1), 0,1)
Where A1 is the column of requests in comma delimited forme (ie: "AB,CD,EF").
Where B1 is the column containing the person assigned to shift 1 (ie: "AB")
In this case, would return a "1" as no error was returned, as AB was found in the list. Here "1" would represent a schedule conflict.
Without creating many cells for each shift- there are 20 shifts- can I create an array or string together this type of "SEARCH" function?
I have data in range C1:C1000
The data is numbers 14-digits
When I try to convert the data to text the data appears like that
3.00052E+13
I want the data to appear like that
30005221800911
When I double click in the cell the problem solved but it will impossible to double click 1000 cells ...
Is there a way to combine cells in excel and chage the color of part of it?
Example: On my sheet I have multiple cells I need to combine:
Say A1 reads 23.65
Say A2 reads 43.65
Now on A3 I want it to read "Estimated budget 23.65 vs 43.65"
I can get this to work but here is the problem, I want the 23.65 to always be red and bolded and the 43.65 to always be blue and bolded.
I noticed that I have several (@200) comments that when visible displays Cell X Commented by (my work user number) in the lower left corner of the excel window. I'm not sure how to change this so its the company name instead of my work number. I'd be using a loop to go through each comment but need help with the line that would change this part.
View 8 Replies View RelatedI have a worksheet, where i type in the id of a member and its uses vlookup to search that member, i enter all the other data required. I am designing a library system and need to record a loan. I have code off another thread that "Copy Cell On Each Change To Next Blank Cell In Column" The problem with this is that when i implement it in my system i change the id cell and that changes the member but when i changes the ranges in the code it doesn't recognise a change has been made, because of vloookup. Current code i have got for the page is
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$D$3" Then Exit Sub
Application.EnableEvents = False
Range("A65536").End(xlUp).Offset(1, 0).Value = Target.Value
Application.EnableEvents = True
End Sub
share a macro to copy cells from range C20:C300 and paste them in C2 on multiple lines. If possible two lines gap between each cell's value when they are pasted in C2.
View 9 Replies View RelatedI have a text box. I want it to pull the data from Cell B2 and list it in the text box. The problem is that I want to be able to change the data if the user clieck inside the box and changes it.
View 3 Replies View RelatedIs there a way to change the text in a cell to proper apart from 2/3 letter words which I want to keep as upper? Basically can it ignore all words that are 2 or 3 letters long, but change all other words to proper text?
View 3 Replies View RelatedThe code below will put "Some text" into column B when data is pasted into column D. This only works when copying data into one cell. If I copy into multiple cells of column D then the code does not run at all.
[Code] ......
I'd also like to have it be able to change the 2 cells to the right, in columns (M&N) whenever the conditional value given below are true. I thought I had posted this message yesterday but never saw it show up as a thread.
Private Sub Worksheet_Change(ByVal Target As Range)
Set MyPlage = Range("L1:L800")
For Each Cell In MyPlage
If Cell.Value = "Hold" Then
Cell.Interior.ColorIndex = 3
End If
If Cell.Value = "Complete" Then
Cell.Interior.ColorIndex = 4
End If
If Cell.Value = "Issued" Then
Cell.Interior.ColorIndex = 43
End If
If Cell.Value = "Release" Then
Cell.Interior.ColorIndex = 36................
I'm trying to run a macro anytime cells G2, J2, M2, O2, P2, S2, V2, Y2, AB2, AE2, AH2, AK2, AN2, AQ2 change.
Right now I have the below code which is working well, but I only have it set for G2. Do you know what the notation is to make the target range multiple cells?
Also, I use the xlDown command in my sorting code, but later on it reverts back to "A5:AT60"... is there any way I can remove these specific cell references? I want to avoid having to re-write the macro every time my selection shrinks or expands.
Code:
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("G2")) Is Nothing Then
Rows("5:5").Select
[Code]....
creating a macro. I would like the background color of certain cells under column A (cells 12:25, 27:32, 34:38, 40:45, 47:51, 53:61, 63:70, 72:79, 81:88, 90:92) to change to the color green and display the text "Complete" on mouseclick.
Before the cell is clicked, it would be the color red, with no text. (I'm not sure if you need to know that).
Next, I would need a macro that resets everything back to red. I hope this macro could be assigned to a form button.
How to colour cells depending on text in other cells, for example,
I would like cells D26:AA26 to turn light red if the letters 'FSM' are in cell 'E26' even if other text appears in it, eg 'SA+/FSM'
I have been playing about with conditional formatting but can seem to solve the mixed text issue?
I have included an example below,
Do you use a "*FSM*" around the formula?
[URL] ...........
I have tried to go around the long way to achieve this but came up with pages of pointless code .... I know there is a better way I just dont know enough about VB to do it myself ... And I know this is EASY for many :-)
--------
Cell ranges h11 to as11 are a totals row.
If the total is 0, colorindex is set to vbpatternnone, if >= 1, then colorindex is set to vbpatterngray. Easy right ? I just dont kn ow how to do FROM/IF/DO range loops...
--------
Details:
The code in worksheet_SelectionChange will contain the following:
1: From range h11 to as11, variable1 = application.interior.colorindex of the cell.
2: Check if the cell is >=1 or <=0 ....
3: If >=1 then set application.interior.colorindex = vbpatterngray. Go to #5.
' (This inserts a pattern over the original color of the cell)
4: If <=0 then set application.interior.colorindex = vbpatternNONE
ALSO set application.interior.colorindex = variable1
' (This clears the cell pattern and returns it to original color)
5. Repeat steps to clear cell pattern and restore color / or insert pattern for all cells from range H11:AS11
6. End sub
I have a spreadsheet with several freeforms. I would like them to change backgroundcolour, if the content in a cell is equal to another cell.
Basically I would like my shape "Freeform1" to change backgroundcolour to RGB (0, 180, 0) if Sheet1.Range ("A1") = Sheet2.Range("D3").
If it's not the same content in both cells, I would like the freeform to remain unchanged, that's RGB (79, 129, 189).
This should happend automaticly, so I don't have to click the shape to make it happen.
I have four cells that contain text. All have connected check boxes with TRUE FALSE.
I need to be able to select anyone one of these cells with a check box, and have it's text appear in one separate cell eg: A1.
I have no issue connecting check boxes etc. I have no issue reproducing the text from any of these cells into multiple cells with a check box. But they have to be selectable and reproducing in one cell only (eg"A1").
Is there a way to Find & Replace Multiple Cells that contains the same text.
For example:
Cell A1 contains: Brand Stanley Stock No. ASJK12311
Cell A2 contains: Brand Facom N/A 5456chis
Cell A3 contains: Brand 3M Stock No. 128931
Now, I want to replace all cells that contain the word "Stock No" to "Free Delivery"
Result should be:
Cell A1 contains: Free Delivery
Cell A2 contains: Brand Facom N/A 5456chis
Cell A3 contains: Free Delivery
I've tried a few methods but none of them work.
I need to remove the last bit of text from the contents of multiple cells in a column. In my attached example, I want to remove the last line break and the text "• List Price £", by processing all the cells in the column at the same time.
I can add text OK in Format: Cells: Number: Custom using @"text" but haven't been able to figure out how to remove text yet!
I need to combine or merge text from cells in 20 rows of data into a format able to be printed. This is evaluation data for a class of 20 students, where each row is one students response to the evaluation, with the last cell in the row containing their comments (text data that may be blank, or up to several sentences in length). Here's what I'm trying to do:
Combine all 20 cells into one cell that can be printed (with word-wrap turned on).
Add a blank line between each comment.
Disregard any blank cells so extra blank lines are not added.
Can excel copy text from multiple cells into one cell?
View 9 Replies View RelatedI am looking for a formula which will sum numbers with less than symbols in front of them (i.e. they are text cells), and then replace the less than symbol again in the summed cell if initially present. The problem is that not every cell contains a < symbol, some of them are just numbers, and not every column contains a < symbol either.
e.g. I am looking for a formula which if entered in row 5 of this example would give these answers.
A
B
C
D
1
<0.001
<0.1
0.2
<0.01
2
<0.0001
0.1
0.2
<0.1
3
<0.0001
<0.2
0.2
<0.1
4
<0.001
<0.1
0.2
<0.01
5
<0.0022
<0.5
0.8
<0.22
I have two separate data files that I am trying to combine into one worksheet. One set of data looks something like this:
Column A Column B ColumnC
Date1 Name1
Date1 Name2
Date1 Name3
Date2 Name1
Date2 Name2
Date2 Name3
This is repeated for something like 200 dates, and there could be anything from 1 to 10 lines for each date, for a total of over 1000 rows.
The other data file has this information:
Column A Column B ColumnC
Date1 Place1
Date2 Place2
Date3 Place3
I.e. one line for each date.
I want to combine the data so that Column C contains the Place data. All I could think to do was combine them and then sort by Column A, so I ended up with this:
Column A Column B ColumnC
Date1 Name1
Date1 Name2
Date1 Name3
Date1 Place1
Date2 Name1
Date2 Name2
Date2 Name3
Date2 Place2
And then I would just copy and paste to end up with this:
Column A Column B ColumnC
Date1 Name1 Place1
Date1 Name2 Place1
Date1 Name3 Place1
Date1 Place1
Date2 Name1 Place2
Date2 Name2 Place2
Date2 Name3 Place2
Date2 Place2
Ultimately I want to sort the combined data by Column C, I just have to get the data in there first.
Is there a formula or macro or something I could use to copy the Place names automatically? Each worksheet with 1000 lines and several dozen worksheets means I'd have to copy and paste dozens of thousands of times, which is a bit tedious.
i know this is easy but i don't have an idea how to do it. I have dates written on column A1:A7 like
29-Mar-09
30-Mar-09
31-Mar-09
1-Apr-09
2-Apr-09
3-Apr-09
4-Apr-09
what I would like to do is how can I combine 29-Mar-09 and 4-Apr-09 into one cell...I would like the output to be placed in cell A8 appear as "29-Mar-09 to 4-Apr-09".
I have created a sheet which gets populated by information but have left out the top row. I want to use the tope row to enter my header fields but was looking to see if there a more effcicent way of doing this.
I am using the following code below but was wondering if there was a way using arrays as variants or ranges to do it more efficiently.
I want to count cells that contain specific text but multiple criteria. For example in range B4:B12 I want to count the cells that contain the word 'daily' AND the word 'weekly'. Each cell in the range contains one word.
View 5 Replies View RelatedI want to conditional format based on all of the following cells contain the same text as AT19 or not.
A19 H19 O19 V19 AC19 AJ19
What is my formula?