Free Microsoft Excel 2013 Quick Reference

Merge cells to make a date Results

I have 3 seperate cells each with a number, for example, D2 has number 4, E2 has number 21 and F2 has number 2009. I need to combine these 3 cells to make the date 4/21/2009. I know this can't be as hard as I am making it out to be.

Does anyone have any ideas? Thanks for your help.

Hello everyone,

first thanks to the forum for already helping with many other issues I have had in VBA.

Now a problem for which I could not really find a solution yet:

I am currently copying values from a workbook that is in an older format (variable called Oldws) to a workbook in a new format (called Masterws). I cannot change the format so e.g. merged cells have to stay merged. For the most part the VBA program now does what it should do but I have an issue with one item, the date.

I am copying a date from the old format to the correct cell in the new format (Location is "Masterws.Worksheets(1).Range("E65")") and it basically does that.
Problem is that the date from the OLD format can have a different forms (e.g. 2006.08.07 or 07, Aug. 2006 or 07-August-2006 etc).

Now the cell in the new workbook usually automatically makes a date in this format (07-Aug-2006) when someone enters a value manually.
However, copying the value from the old merged cell into the new merged cell does not trigger that behavior. The only way so far is to manually go to the date cell and either doubleclick or use F2 and ENTER.
I tried to use

	VB:
	
Masterws.Worksheets(1).Range("E65").MergeArea.Select 
SendKeys "{F2}", True 
SendKeys "{ENTER}", True 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
but get an error 1004 'Select Method of Range class failed' for the merged cell.
Using this code

	VB:
	
Range("E65:F65").Select 
SendKeys "{F2}", True 
SendKeys "{ENTER}", True 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
ends up with the program trying to constantly opening the new format file. Guessing that the sendkeys commands now trigger opening the master file instead of being applied to the cell.

So is there some way to either apply the sendkeys commands specifically to the merged cell in the new formatted workbook on sheet 1 or is there maybe even some better way that ensures that the date copied over is changed to the new format regardless of what the old date format is?

Best regards,

Phil

Hi,
Almost everyday I have to insert a comment with the date of today
So the oldest date and comment have to be deleted
And the date of today has to be inserted. Also oldest comment has to dissapear and has to be new space in most above comment
Only insert new date of today in columns where there is data in the row
What might makes it bit more difficult is the merged cells in column A B C and D
So every 8 rows this has to be done (8 cells are merged)
IN sheet 2 I made an example how it should be

See attached file

Greetings-

I'm currently working on a Speedsheet in Excel. My plan is to click on a cell that is merged with other cells to make one large cell. Upon clicking the cell the date will appear........sounds easy right! Well I have a code to enter the date fine but when I merge a few other cells together the date will not show up or should I say the code doesn't work. I have the following Format below. Any help with the code would be great thanks. I don't know if it will help but the cells being used are A-D10 and A-D11. Also one last thing I in the stone ages working with Excel 97

Case Range("A10").Address

Target = Format(Date, "MM/DD/YY")

End Select
End Sub

Hi there,

I have a need for the following:

I want to be able to select a variable amount of rows (i.e. it could be 2 rows, 5 rows, 50 rows etc.) with a variable amount of data columns (i.e. could be 5 columns wide, 20 columns etc.) and be able to combine the data into 1 row, with the data from each row being separated by an alt+enter (or char(10)).

Constraints:
1) I do not want to show duplicate data (i.e. if b2 and b3 are the same value, I only want to show the value once.)
2) Data could be text, numbers, dates, etc. (we can treat the values as strings)
3) I want the results to override the first row in the selection and delete all the other rows in the selection (i.e if i select rows 1 and 2, I want the result to be in row 1, and have row 2 deleted)
4) Needs to be able to be run from personal.xlsb (not saved within the actual workbook)

I have attached a sample workbook to make it easier to understand what I am asking for: combine-project.xlsx.
In this example assume I selected rows 2 and 3. The result I am looking for is row 9 (which should actually overwrite row 2, and row 3 would get deleted.

I am assuming there will need to be a userform created with the refedit control to select the range, then have an executable command button to run the routine...

Any help would be greatly appreciated.

Thanks!

I have a worksheet that dumps the username, date and what cell was modified into another worksheet. Is there a way to make the modified cell value in my report sheet a link to the actual cell on the original sheet? Auto Merged Post;

Awesome

I have finally got the loop to do what I want (tried Pivot-table graph but they are upset by missing values).

	VB:
	
 NCRtest() 
     '
     ' Non-Conformance Report Macro
     ' Macro created 2/11/2006 by aborlase
     '
     ' Setup numbers
    Dim Lines As Integer 
    Dim Status As Integer 
    Dim TTC As Integer 
    Dim Cause As Integer 
    Dim NCRs As Variant 
    NCRs = Array(2, 8, 14) 
     
     ' Make everything look hidden and fast
    ActiveSheet.Name = "Data" 
    Application.ScreenUpdating = False 
    Application.DisplayAlerts = False 
    Application.StatusBar = "Summarising NCR Export for Report" 
    ActiveWindow.Zoom = 75 
     
     'Get rid of blank sheets (there can only be one!)
    Dim sh As Variant 
    For Each sh In Sheets 
        If Not IsChart(sh) Then 
            If Application.WorksheetFunction.CountA(sh.Cells) = 0 Then sh.Delete 
        End If 
    Next sh 
     
     ' First find out how big
    FinalRow = Cells(65536, 1).End(xlUp).Row 
    FinalCol = Cells(1, 255).End(xlToLeft).Column 
     
     ' Change to "proper" dates and insert summary columns and sort
    Sheets("Data").Select 
    Columns("C:G").NumberFormat = "d/mm/yy;@" 
    Columns("A:E").Insert Shift:=xlToRight 
    Columns("A:B").NumberFormat = "0" 
    Range("A1").FormulaR1C1 = "Code" 
    Range("A2").FormulaR1C1 = "=RC[13]&RC[2]&RC[18]" 
    Range("B1").FormulaR1C1 = "=COUNT(R[1]C:R[" & FinalRow & "]C)" 
    Range("B2").FormulaR1C1 = _ 
    "=IF(AND(RC[9]>0,RC[12]=2),RC[9]-RC[7],TODAY()-RC[7])" 
    Range("C1").FormulaR1C1 = "TTC" 
    Range("C2").FormulaR1C1 = _ 
   
"=IF(RC[-1]>150,8,IF(AND(RC[-1]99),7,IF(AND(RC[-1]74),6,IF(AND(RC[-1]49),5,IF(AND(RC[-1]29),4,IF(AND(RC[-1]15),3,IF(AND(RC[-1]5),2,1)))))))"

    Range("D1").FormulaR1C1 = "Cost" 
    Range("D2").FormulaR1C1 = "=VALUE(RC[17])" 
    Range("E1").FormulaR1C1 = "Days" 
    Range("E2").FormulaR1C1 = "=VALUE(RC[19])" 
    Range("A2").Resize(FinalRow - 1, 5).FillDown 
    Cells.Sort Key1:=Range("N2"), Order1:=xlAscending, Key2:=Range("C2") _ 
    , Order2:=xlAscending, Key3:=Range("S2"), Order3:=xlAscending, Header:= _ 
    xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
    DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _ 
    xlSortNormal 
    Range("A1").Resize(FinalRow, FinalCol + 5).Name = "ncr" 
     
     ' Insert Summary Worksheet and names
    Worksheets.Add After:=Worksheets(1) 
    ActiveSheet.Name = "Summary" 
    ActiveWindow.Zoom = 75 
    Range("A2").FormulaR1C1 = "Cause" 
    Range("A3").FormulaR1C1 = "0" 
    Range("A4").FormulaR1C1 = "1" 
    Range("A5").FormulaR1C1 = "2" 
    Range("A6").FormulaR1C1 = "3" 
    Range("A7").FormulaR1C1 = "4" 
    Range("A8").FormulaR1C1 = "5" 
    Range("A9").FormulaR1C1 = "6" 
    Range("A10").FormulaR1C1 = "7" 
    Range("A11").FormulaR1C1 = "8" 
    Range("A12").FormulaR1C1 = "9" 
    Range("A13").FormulaR1C1 = "10" 
    Range("A14").FormulaR1C1 = "11" 
    Range("A15").FormulaR1C1 = "12" 
    Range("A16").FormulaR1C1 = "13" 
    Range("A17").FormulaR1C1 = "14" 
    Range("B2").FormulaR1C1 = "Name" 
    Range("B3").FormulaR1C1 = "Health & Safety" 
    Range("B4").FormulaR1C1 = "Design - Incorrect Info supplied" 
    Range("B5").FormulaR1C1 = "Supply - Incorrect Info supplied" 
    Range("B6").FormulaR1C1 = "Construct - Incorrect Info Supplied" 
    Range("B7").FormulaR1C1 = "Spare" 
    Range("B8").FormulaR1C1 = "Spare" 
    Range("B9").FormulaR1C1 = "Environmental Hazard" 
    Range("B10").FormulaR1C1 = "Design - DWG incorrect" 
    Range("B11").FormulaR1C1 = "Design - DOC/SPEC incorrect" 
    Range("B12").FormulaR1C1 = "Supply - Not to DWG" 
    Range("B13").FormulaR1C1 = "Supply - Not to DOC/SPEC" 
    Range("B14").FormulaR1C1 = "Supply - Transport/Packaging" 
    Range("B15").FormulaR1C1 = "Construct - Not to DWG" 
    Range("B16").FormulaR1C1 = "Construct - Not to DOC/SPEC" 
    Range("B17").FormulaR1C1 = "Construct - Storage/Handling" 
    Range("C2").FormulaR1C1 = "TCC1" 
    Range("D2").FormulaR1C1 = "TCC2" 
    Range("E2").FormulaR1C1 = "TCC3" 
    Range("F2").FormulaR1C1 = "TTC4" 
    Range("G2").FormulaR1C1 = "TTC5" 
    Range("H2").FormulaR1C1 = "TTC6" 
    Range("I2").FormulaR1C1 = "TTC7" 
    Range("J2").FormulaR1C1 = "TTC8" 
    Range("C1:J1").Select 
    With Selection 
        .HorizontalAlignment = xlCenter 
        .VerticalAlignment = xlBottom 
        .WrapText = False 
        .Orientation = 0 
        .AddIndent = False 
        .IndentLevel = 0 
        .ShrinkToFit = False 
        .ReadingOrder = xlContext 
        .MergeCells = False 
    End With 
    Selection.Merge 
    ActiveCell.FormulaR1C1 = "Open" 
    Range("K2").FormulaR1C1 = "TCC1" 
    Range("L2").FormulaR1C1 = "TCC2" 
    Range("M2").FormulaR1C1 = "TCC3" 
    Range("N2").FormulaR1C1 = "TTC4" 
    Range("O2").FormulaR1C1 = "TTC5" 
    Range("P2").FormulaR1C1 = "TTC6" 
    Range("Q2").FormulaR1C1 = "TTC7" 
    Range("R2").FormulaR1C1 = "TTC8" 
    Range("K1:R1").Select 
    With Selection 
        .HorizontalAlignment = xlCenter 
        .VerticalAlignment = xlBottom 
        .WrapText = False 
        .Orientation = 0 
        .AddIndent = False 
        .IndentLevel = 0 
        .ShrinkToFit = False 
        .ReadingOrder = xlContext 
        .MergeCells = False 
    End With 
    Selection.Merge 
    ActiveCell.FormulaR1C1 = "Outstanding" 
    Range("s2").FormulaR1C1 = "TCC1" 
    Range("t2").FormulaR1C1 = "TCC2" 
    Range("u2").FormulaR1C1 = "TCC3" 
    Range("v2").FormulaR1C1 = "TTC4" 
    Range("w2").FormulaR1C1 = "TTC5" 
    Range("x2").FormulaR1C1 = "TTC6" 
    Range("y2").FormulaR1C1 = "TTC7" 
    Range("z2").FormulaR1C1 = "TTC8" 
    Range("S1:Z1").Select 
    With Selection 
        .HorizontalAlignment = xlCenter 
        .VerticalAlignment = xlBottom 
        .WrapText = False 
        .Orientation = 0 
        .AddIndent = False 
        .IndentLevel = 0 
        .ShrinkToFit = False 
        .ReadingOrder = xlContext 
        .MergeCells = False 
    End With 
    Selection.Merge 
    ActiveCell.FormulaR1C1 = "Closed" 
     
     ' Now do For...Next...If loop for each line/Status/TTC/Cause
    For Lines = 2 To FinalRow 
        For Status = 0 To 2 
            For TTC = 1 To 8 
                For Cause = 0 To 14 
                    Check = Status & TTC & Cause 
                    If Cells(Lines, 1).Value = Check Then 
                        NCRs(Status, TTC, Cause) = NCRs(Status, TTC, Cause) + 1 
                    End If 
                    Application.StatusBar = Lines 
                Next Cause 
            Next TTC 
        Next Status 
    Next Lines 
     
     ' We have all values now lets place them into the summary
     
     
     
     ' Make everything look normal again and show problems if there
Exits: 
    Sheets("Summary").Cells.EntireColumn.AutoFit 
    Application.ScreenUpdating = True 
    Application.DisplayAlerts = True 
    Application.StatusBar = False 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Now if people can bypass the mess I have made of the text in the summary sheet, and look at the loop. I have likely over detailed items, and taken the hardest way to 'make' a next sheet. I am still learning.

In short we are trying to summarise which line entry matches 3 criteria for graphing.

The summary table looks like has the Causes on the y, and 3 sets of 'TTC' across for each status.

How do I get the values from the array into the right cells? or is it best just to 'add' the values in the cell directly? (is this possible?)

Hi,
I need help making a worksheet merge selected cells by the user and generate a random background colour. If this is not possible then just merging the selected cells would do.

basically i am making a sheet to record people coming in and out on specific dates so i have the dates in columns and room nos in rows. i wont to be able to say mr. bla came in on and left on, but dont want his name in every individual cell.

also how can i make a worksheet bigger? the columns stop at IP and when i click add column nothing happens.

Thanks in advance of any help.
Andy.

How do you sort rows of data that are merged for the first few columns and then normal for the remainder?

Example, I have a spreadsheet that tracks PLAN dates against ACTUAL dates for a Sales Order. this means that Order detail is merged across two rows.

I want to sort the data in ascending order of one of the merged rows but i get an error message,

"This operation requires the merged cells to be identically sized"

This does make sense, so how would I do this seemingly normal operation ?
Your help will be greatly appreciated.

Simon.

I have a spreadsheet that has many columns, essential I have a header
for a group of three columns which is a merge of three column cells to
make one cell and has a date (01/01/08) in it formatted to "mmm/yy". I
want to apply a conditional format tot the merged cells within the
range that has the current month in it. here is an example of what I
have:

Starts in column E2 on Row 2
(I want to highlight the merged cells that equal the current month
based on date in $A$1
‚€€€€€€€€€€€€€ˆ€€€€€€€€€€€€€ˆ€
 Jan-08  Feb-08 ------->
Dec-08
†€€ˆ€€€€ˆ€€€€€Š€€ˆ€€€€ˆ€€€€€Š€
HrsEmps # Jobs HrsEmps # Jobs 
„€€‰€€€€‰€€€€€‰€€‰€€€€‰€€€€€

Thanks, sorry about the crude drawing. but wanted to make sure it was
understood.

Hi,

I have a spreadsheet as per the enclosed attachment. The top date is
always Today's date, regardless of when the spreadsheet is opened. The
rest of the dates are Today's date plus 1, plus 2, plus3, etc.
Therefore, the dates are moving up the table, day by day. The Saturdays
are linked to the correct dates by using the
=IF(WEEKDAY(F6)=7,"Saturday", "") function, so they are moving up the
screen as well.

The horizantal lines between the weeks move correctly due to
Conditional Formatting (thanks to bpeltzer for that tip).

I would like to merge the blocks of cells that make up a week in such a
way that the merged group of cells moves up the table correctly with
their corrosponding dates.

I have manually merged the top row of cells in the attachment to
indicate what I intend to do, but tomorrow, when the dates have moved
up one, it will look all wrong again.

Is what I want to do possible, and if so, how is it done?

many thanks

Spike

+-------------------------------------------------------------------+
|Filename: dates.jpg |
|Download: http://www.excelforum.com/attachment.php?postid=4064 |
+-------------------------------------------------------------------+

--
SpikeUK
------------------------------------------------------------------------
SpikeUK's Profile: http://www.excelforum.com/member.php...o&userid=28997
View this thread: http://www.excelforum.com/showthread...hreadid=487999

I'm using an Excel 2007 worksheet to arrange school schedules, with columns
for date, session time, teacher name etc. Each day covers three rows, with
one cell for each session that day.

To make it easy to read, it is useful to have one column with some cells
merged (for the dates, and also to show that a particular course is running
over several days or weeks).

I've set up a pivot table referring to this data, but obviously the cells
'behind' the merged cells are blank. is there any way of getting all the
cells within a merged area to contain the same data? or another neat
workaround?

For the dates, i've set up a formula in another column to have multiple
cells referring to a single location. The trouble is that the courses are all
different lengths, and don't follow a particular pattern.

thanks for your help.

I attached the sheet in which I would like this to apply, particularly because it already contains some code under the Sub Worksheet_Calculate() and I wanted to make sure it got merged with this new code ok.

Basically I just want a macro to Check M2:M2000 and see if any of the dates that appear there are older than 30 days. If they are then I would like it to put up a MsgBox displaying any overdue CARs and would like the msgbox to display the CAR # in column A associated with the overdue date in Column M.

For example, looking at the attached sheet- the msgbox would pop up when the user selects the log sheet and it would say:

"Todays Date is 8/28/2009
CAR # 236 was due on 7/26/2009 and is now overdue!
CAR # 239 was due on 7/24/2009 and is now overdue!"

and then the user would click "ok" to get out of the msgbox

I appreciate the help!

Private Sub Worksheet_Calculate()
    Dim cell        As Range

    For Each cell In Intersect(Me.UsedRange, Range("CY2:CY2000"))
        Select Case cell.Value
            Case Empty
                cell.Interior.ColorIndex = xlColorIndexNone
            Case "Pending Approval"
                cell.Interior.ColorIndex = 38
            Case "Open"
                cell.Interior.ColorIndex = 45
            Case "Pending Close"
                cell.Interior.ColorIndex = 6
            Case "Closed"
                cell.Interior.ColorIndex = 35
        End Select
    Next cell
End Sub


So, if I have "1/2/2010" in a cell, I want to display is as:

Sat
Jan
2

which is vertically wrapped to three cells.

I can use a custom format "ddd mmm dd" which displays the text I want, but doesn't wrap to the next cells, even if I shrink the width of the column (I get "###"). Choosing to wrap and/or merge cells doesn't seem to make any difference.

Is there a code I can include in the formatting string to force a line feed or wrap?

Thanks,
Heath

Hi,

I have a spreadsheet as per the enclosed attachment. The top date is always Today's date, regardless of when the spreadsheet is opened. The rest of the dates are Today's date plus 1, plus 2, plus3, etc. Therefore, the dates are moving up the table, day by day. The Saturdays are linked to the correct dates by using the =IF(WEEKDAY(F6)=7,"Saturday", "") function, so they are moving up the screen as well.

The horizantal lines between the weeks move correctly due to Conditional Formatting (thanks to bpeltzer for that tip).

I would like to merge the blocks of cells that make up a week in such a way that the merged group of cells moves up the table correctly with their corrosponding dates.

I have manually merged the top row of cells in the attachment to indicate what I intend to do, but tomorrow, when the dates have moved up one, it will look all wrong again.

Is what I want to do possible, and if so, how is it done?

many thanks

Spike

I am trying to write a macro that concatenates the content of an unknown number of rows in to a single cell and separates the values with a CHAR(10). The groups of data I want to concatenate are separated by a blank row so I imagine that the Macro will need to be terminated via a check for two blank rows on the trot or the presence of a terminating value (in my feeble attempts so far I have used the word 'end' as the terminator). Its complicated further by the fact that some of the cells that need to be merged contain date & time values so they will need to be converted to text.

Below is the skeleton of my initial attempts at getting my head around the algorithm but the main function of actually concatenating the cells is missing.

I have also attached an example worksheet that shows the original data and the desired output (ignore the cell merging, that’s just to make it easier to read).

I hope this makes some kind of sense... Thanks in advance for your help :-)

Stu

Sub Merge_Progress()

selectRow = 1
selectColumn = 1

Cells(1, 1).Select

Do While Not ActiveCell = "end"

    If ActiveCell = "" Then
        selectRow = selectRow + 1
        Cells(selectRow, selectColumn).Select
    Else
        Do While Not ActiveCell = ""
            'Add the content of the current cell to the concatenated cell
            selectRow = selectRow + 1
            cells(selectRow, selectColumn).select
        Loop
    End If
Loop
End Sub


Hi everyone,

This one should be a nice one for the experts. Attached please find an example file.

The file is based on a Gantt chart and each project has 4 rows that need to stay together.

I would like to be able to sort the various projects, based on different type of criteria:

1.) project start data (advertising date)
or
2.) project leader and then by start date

As you can see the problem is that I am trying to sort data but the four rows always need to stay together in the same order.

Now, the solution I had thought of was to create some hidden columns, one numbered with:

1
1
1
1
2
2
2
2
The second one with:
1.1
1.2
1.3
1.4
2.1
2.2
2.3
2.4

Then, when applying the sort mechanism based on these columns, the data would be sorted in the correct way.

The problem here is though that as there might be different people working on this file, these numbers might not always be replicated correctly (copying and deleting of rows for example by unexperienced users).

Is there any more elegant way to do this sorting of multiple rows? Besides, I also have merged cells which make life more difficult, but I can remove these, if this helps to get the problem "sorted" ;-)

Thanks a million!
Titus

This is slightly confusioning.

Are you able to specify the certain cell range that you want selected based upon how many rows make up a differnet certain merged cell?

I ask, because my range of date changes depending upon how many rows the user deletes or inserts. The one constant that I can think of to reference my automatic selection back to is, the number of rows within the merged cell that is adjacent to my data range that I want selected.

If you look at the attached. File - Sample12-30.xlsm

I know there are different ways to select this data, but my goal here is to select data in Column H (only the dates that make up each phase). For instances, at different points in my custom macro, I want to select only the months that make up "Phase 2" and at other times I want to select the months in "Phase 1" and also "Phase 3."

My current solution is to name the range (for instance in my sample doc, name H7:H12 - "months_in_phase1" and just call it out when needed.
But my problem is that, that range of data is dependent upon how many rows the user inserts or deletes. If the user deletes
the last row of the name range in one instance and inserts another (which is possible) - then that name range quite isn't
what it needs to be. 

THEREFORE, my only solution is to think that I need to copy only the number of rows (in Column H) that are contained in the merged cell titled 'PHASE #' - e.g so if "PHASE 2" contains 10 merged cells, then only copy 10 rows in Column H. So my question is, can that be done and how?

THANK YOU SO MUCH FOR LOOKING AT THIS WITH ME.

Hi I'm working on this timeline that is suppose to show the start and the end date for a certain number of tasks.Please see pics for a better idea. The dates are shown week by week (mondays) and they will change respecting to the program that they are related. So what I want to have is a timeline that will autopopulate the dates respecting to that first date. What i did was to relate the first cell in each row (year, month, day) with the start date that is on a separate sheet. Then I sum 7 days all the way to the right and for a row im displaying the years, for the other one the months and for the other one the days.

The problem is that to make it look better I want to merge all the cells that contain the same month or the same year. when i do this by using the merge and center button the sequence gets messed up because it is only using the value in the first merged cell and deleting the others.

Is there a way in which i can do al this automatically and not having the sequence problem? Or any suggestion of a simpler way of doing this?

thanks for the help!

ok first of I want it to run for a row that contains a formula to vlooup a date then display the month. its cells e1:IV1 I want to end up with something that will make a merged cell with the month centered over all the dates below for the month and be able to update as the dates change. I have it set up to update the calender around todays date so it shows a month before and as many days after today so we can use it to schedule our production against sales.

I'm a rookie at the macro and programming so any help would be greatly appreciated. links or even a full blown macro would greatly help me out

thanks