Macro To Combine 2 Cells In Value Format
Jun 9, 2007
I have 2 cells that dump data in a column format. I need to combine these two cells with a ' after the data in the column. This macro will loop down until both column J and K are empty,
I need a Macro that
1. Will combine both cells
2. Data will be value format not formula
3. Macro Will run as soon as workbook is opened
Ex.
F J K
1 Prop MD MD Objective
2 1' Test Data 1 Test Data
3 235' next data 235 next data
View 4 Replies
ADVERTISEMENT
Jul 2, 2014
I need to combined all non blank cells in a given row into 1 cell. But within each row i have 5 phases that the values fall into, which is denoted by the 1st charter 1, 2, 3, 4, or 5. for example, in a given row i have 1-a, 1-c, 2-d, and 1-f and these values occurs in non consecutive columns starting from G to ALR. I need a macro that sorts these values in one of the 5 phases. So in another sheet the macro would combined [1-a 1-c 1-f] in Phase 1 and [2-d] in phase 2. and if there are other phases it would put them in the appropriate cell.
See attached workbook : Work Order Summary Sheet.xlsx
View 3 Replies
View Related
Jul 17, 2014
However, in addition to what the macro already does, it is possible to add another work sheet in the same work book that outputs the values in separate cells? It also has to output the data in order: for example, in the first work sheet the data is inputted at random and has spaces but the macro will have to remove all the blank cells and output them in order based on the first value in the test string, 1, 2, 3, 4, and then 5;
input:
blank 3-x Blank 1-y blank 2-z 1-k
output:
1-k 1-x 2-z 3-y
View 4 Replies
View Related
Jun 8, 2014
Creating a macro that is a combination of other cell values. the cell order is listed below:
C57 = contains a unique number that will change from time to time eg. 1234
H57 = 1 (this will always be the value of 1)
N57 = contains text eg. AUDUSD
V57 = contains text eg. BS
AB57 = contains a number eg. 1.8954
AH57 = 0 (this will always be the value of 0)
AN57 = contains a number eg. 0.01
AS57 = contains a number eg. 1.7954
AZ57 = contains a number eg. 1.9954
BH57 = conatins either 1 or 0 eg. 1
the cell needs to contain the above examples like this :
BT57 =OpenPosition(1234,1,"AUDUSD","BS",1.8954/0,0,0.01,1.7954,1.9954,1)
This will be needed for a total of 14 rows (57, 59, 61, 63, 65, 67, 69, 71, 73, 75, 77, 79, 81, 83)
The cell with the results cannot have any cell references contained in the end results and must be formatted as above so that the add in program can read and pass on the information.
View 3 Replies
View Related
Sep 26, 2013
I need a macro that will combine/consolidate rows when cells from 2 separate columns match. example...
beginning:
Column A column B column C column D column E
row 1 Seminole 80 unleaded 1064 100100
row 2 Seminole 36 clear dsl 825 100100
row 3 Seminole 80 unleaded 1101 100100
row 4 Seminole 30 dyed dsl 3421 100100
This is what I need the macro to do:
Column A column B column C column D column E
row 1 Seminole 80 unleaded 2165 100100
row 2 Seminole 36 clear dsl 825 100100
row 3 Seminole 30 dyed dsl 3421 100100
View 2 Replies
View Related
Jul 5, 2012
I have a spreadsheet that uses a macro to format it. It’s quite simple and the code is shown below:
Code:
Range("A1:D2").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
[Code] ...........
The spreadsheet will have info constantly added on (downloaded from a CSV).
What I would like the spreadsheet to do is look at the cells and only format those that have data in them but are not already formatted – otherwise I’ll just end up re-formatting everything every time and as the spreadsheet gets bigger it'll take longer and longer.
View 6 Replies
View Related
Jun 5, 2014
I would like to make a macro to format all my cells.
I need it to format my last digit to superscrift IF there is 4 digit after decimal point.
How to do this?
View 14 Replies
View Related
Nov 23, 2009
DoneyOte on this but i have added to the range and im now getting an error that i can't seem to fix
View 12 Replies
View Related
Apr 27, 2013
A
B
C
D
[Code]....
There are four sheets (labeled A,B,C)
Depending on the contents in cells in Columns A and B (which have 1000+ rows), I want to change the background color of a referenced cell elsewhere:
For each row (lets say from 2-1000)
1. If "A" cell is blank -- referenced cell (address in columns C & D) should be colorless -- (as in Row 2 --- cell Z5 in Sheet "C" should be colorless)
2. If "A" cell has text AND "B" cell is blank, reference cell should be colored BLUE -- (as in Row 3 --- Cell F3 in Sheet "C" should be BLUE)
3. If both "A" and "B" cells have text, reference cell should be colored YELLOW -- (as in Row 4 --- Cell C5 in Sheet "B" should be YELLOW)
While I could do this with a conditional format in every referenced cell, it would take too long to enter them.
View 7 Replies
View Related
Mar 27, 2014
I need Macro to Format all cells in column according to value. The cutoffs are >20, between 21 and 50 (>21;<50) and greater than 51 (<51).
I have macro to consolidate cells from multiple files to single workbook. Now I have to 'color code' the value in all cells in column 'B', all as part of one large macro.
View 1 Replies
View Related
Sep 6, 2007
I have a work sheet with around 50 odd sheets. What i want is to list out all the cells that are in perticular format
e.g.....
View 9 Replies
View Related
May 2, 2008
ABCDEFGHI need to create a macro to perform some conditional formatting1DateDayTypeTimeConfirmed Order No.Site Location2SHEET OVERVIEW301/01/08TuesdayAB4CVertically there will be 366 tables to represent 366 days and Horizontally 10 tables to represent 10 employees,5Dwhich enables the work activities of 10 employees to assigned over the period of a year.6E7AAFIn the type box marked 'A' and 'AA' the user selects from a drop down box a parameter as list below left.8GOn entry of an 'n' in the type box the user can enter data into the 'time','confirmed order','site location' boxes.9HThe idea of having 2 x type boxes is to allow the day to be split into morning and afternoon.10I1102/01/08WednesdayMACRO / VBA FUNCTIONALLITY REQUIRED 1213In the 'type' boxes if the user selects anything other than 'n' then the 4 'site location' boxes and coloured and 14the appropriate text from the list below left is inserted into the 4 associated 'site location' boxes.1516Example:1718Box marked 'A' the user selects 'h'. Boxes marked 'BCDE' and filled with colour and the text holidayBox marked 'AA' the user selects 't'. Boxes marked 'FGHI' and filled with colour and the text trainingType List Entry ParametersWhen the user selects type 'n' after the associated boxes are returned to there blank state (no colour or text)nnormal dayhholidayThe macro/vba would have to respond on everytime a 'type' box changesssickttrainingThere would be 2 x type boxes per day, 366 days a year and for 10 employees. Therefore it would have to monitoruunauthorised absence2 x 366 x 10 (7320) type boxesbbank holidayccompany shutdown
View 9 Replies
View Related
Feb 20, 2007
I'm trying to use this Find Method and combine it with a countif or loop. Something that will count a number of occurences of a unique type of character. I'm looking to find all "F" characters in Bold, Italic and Size 16. Here's my find code that I'm trying to use. I can get it to work by itself but not along with a countif or loop.
Sub count_4()
Dim r As Range
Set r = Range("A1:A6")
With Application.FindFormat.Font
.Bold = True
.Italic = True
.Size = 16
End With
r.Find(What:="F", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, searchformat:=True, MatchCase:=True).Activate
End Sub
View 4 Replies
View Related
Jun 7, 2006
I have a column of cells with values - 0.2%, 0.32%, 0.22, 0.5 etc. The cells with % symbol are in ' Percentage, 2 decimal' format while the plain numbers are in 'General' format i.e. column contains cells in either of these formats. I need a macro where I can specify the column and it will select the cells with the % format, convert it to 'General' and multiply the result by 100 eg. 0.2% converted to 'General' becomes 0.002. When multiplied by 100, result is 0.2 i.e. is displayed without the % symbol.
View 7 Replies
View Related
Nov 26, 2008
In cell B1 I have the number 1
In cell C1 I have the number 1031000
I know how to combine the cells in A1. I am getting a result of 1-1031000
What I would like to happen is that 1 becomes 001.
Ending result is 001-1031000
problem is that cells in B could be one, two or three digits.
On a small scale I could fix manually but i have over 15,000 rows to deal with
View 2 Replies
View Related
Oct 15, 2009
i m working on a spreadsheet and i need cell A+B=C..
Example
Cell A has a first name (John)
Cell B has a last name (Smith)
Cell C has last name then first name seperated with comma
View 4 Replies
View Related
Jan 23, 2013
Is it possible to combine three cells into one? Like for example, column A is the first name, column B is the middle name, column C is last name.
Is it possible put it on a format like this: Last, First M.
(middle name on column B is a name, not an initial.)
And can I make it only the first letters are capped. Last, First M. Or all small letters. last, first m.
View 7 Replies
View Related
Nov 8, 2006
Date:31-Oct-06 (the date is in cell A3)
Formula: "as at"& " "&A3
Result: as at 39021
I need the results to be "as at 31-Oct-06". Is there a formula for this??
View 2 Replies
View Related
Oct 15, 2009
How can I format cells to contain Minutes, Seconds and Hundredths of seconds to be used in calculations eg 1.24.99 means 1 minute and 24.99 seconds. Example calculation is: 1.24.99 - 1.24.90 =0.0.09
View 2 Replies
View Related
Oct 26, 2012
Every month, I have an import a report to a spreadsheet. At my column A, it supposed to be a date format.
For some reasons, I have a combination of a few cells of date format and a few cells of general format with no order at all.
What I need is: Search in the A column, if date format leave it , if not change from general to date format.
View 2 Replies
View Related
Dec 27, 2013
I have a data set that I am generating from a SQL database then putting into excel format. In this spreadsheet, I have a one to many ratio. I want to combine all notes associated with the same applicant_id into one cell. I also, only want the first Received Date and first Entry date that comes across. Is this possible to set up a formula/macro to do this manually for me? Each day I generate this data set, the number of entries can vary. I provided a smaller set, but I'm dealing with hundreds of entries.
ReceivedDateapplicant_identry_datenotes_comments
12/27/13 0:0788695812/27/13 9:07Primary Call
12/27/13 0:0788695812/27/13 9:07Left Note
12/27/13 0:0788695812/27/13 9:08Left Voicemail
12/27/13 0:09886959NULL
12/27/13 0:13886960NULL
12/27/13 0:14886961NULL
[code].....
View 2 Replies
View Related
Feb 25, 2014
I want to combine two cells and add text. Then delete column D.
View 4 Replies
View Related
Dec 1, 2009
I am making a report template in excel. I have written a macro to format the report based on the contents of two other sheets in the work book.
I am attempting to combine two cells in separate sheets as a linked cell in a third sheet.
i.e. in sheet 3, A1 I want to combine the values held in sheets(1).range("a1") with sheets(2).("A1") as a link which will update as the parent cells values are changed.
The cell has to be a link of the other cells as their contents will change based on the report requirements.
View 5 Replies
View Related
Jan 11, 2012
How to combine cells with a similar value inside them into a single cell. Here is what my data looks like.
10'X14'{&}SNM8981-1014{&}16
2'6"X10'{&}SNM8981-2610{&}9
4'X6'{&}SNM8981-46{&}6
[Code]....
For example, I need to have every cell with SNM9012 to combine into the same cell. If I could figure this out it would save me hours vs pasting the similar lines into textmate and removing the enters.
View 1 Replies
View Related
Jun 9, 2009
I am having a hard time with this one. I have 3 cells.
A1 Dallas, TX.
A2 Chicago, IL. (Sometimes Blank)
A3 New York, NY
I would like this info to transfer to another cell like this
Dallas, TX / Chicago, IL. / New York, NY (All in one cell with the "/" to divide.
When the "Chicago, IL." is missing it would look like
Dallas, TX / New York, NY
View 9 Replies
View Related
Jul 9, 2009
I need to search and combine cells based on a specific set name. I would be just as comfortable if it automatically found similar names without me entering the names I would like combined. This is not required though.
EX: Search for John in Column A
Find 2 rows with John
Add each column from row with name John together individually
EX:
Name----Number 1 -----Number 2
John------ 1 -------------3
David----- 55----------- 65
John------ 5 -------------2
In the end I would like:
Name -----Number 1----- Number 2
John------- 6 --------------5
David-------55 ------------65
Then I'll be recalculating fields and repeating the macro for another name (this part I can handle). The current quick macro I made just combines highlighted cells, which is OK but time consuming. I took VB in highschool, but remember none of it!
View 9 Replies
View Related
Nov 30, 2006
am trying to code a loop but I have no idea where to start.
With the help of you guys I have already got some code from a previous thread which gives me a list of hierarchical data now I want to be able to loop through this and actually have the 'parent' next to it.
For example:
1 Shops
2 Butchers
3 Meat
2 Bakery
3 Bread
I would like to be able to say
1 Shops
2 Butchers Shops
3 Meat Butchers
2 Bakery Shops
3 Bread Bakery
View 9 Replies
View Related
Nov 12, 2008
I have a table which has employee names on the left handside. Next to them are contacts in a region.
View 7 Replies
View Related
Feb 18, 2008
I'm new to Macros and need your help. I've read online but can't find instructions on completing what I want.
I have a workbook with about 60 tabs that has various numbers of rows. I want a macro that will combine all the rows from each tab onto a new tab.
Is there an easy marco that i can do to acommplish this?
View 8 Replies
View Related
Feb 27, 2008
I've been Google-ing for the last 45 minutes and tried to combine them myself, but I'm afraid of breaking it.
I believe the second code was taken from the MrExcel site, I don't remember.
CODE 1:
// Track Changes; updates any/all changed cells with a comment identifying the author, date and change that was made //
Option Explicit
Public preValue As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Select Case Target.Column
Case Is = 2, 4, 6
Target.ClearComments
Target.AddComment.Text Text:="Previous Value was " & preValue & Chr(10) & "Revised "
CODE 2:
// Whenever a specific range of cells are updated, the adjacent cells update with a timestamp //
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("F3:F1000"), .Cells) Is Nothing Then
Application.EnableEvents = False
With .Offset(0, 1)
.Value = Now
End With
End If
View 9 Replies
View Related
