| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to line up two dates?
Before I can carry out other task, I have to line up the row where Date 1
starts with Date 2 for all other stocks. It is not always 3 rows down as shown below. How would you write the codes to do that? Thanks. Code Date 1 Date 2 ABW 20090106 5.85 5.85 5.85 5.85 242 20090101 ABW 20090107 5.91 5.91 5.91 5.91 2000 20090102 ABW 20090108 5.85 5.85 5.85 5.85 2380 20090105 ABW 20090109 5.8 5.8 5.8 5.8 3468 20090106 ABW 20090112 5.9 5.97 5.9 5.97 4696 20090107 ABW 20090113 5.93 5.93 5.93 5.93 2993 20090108 20090109 20090112 20090113 Code Date 1 Date 2 20090101 20090102 20090105 ABW 20090106 5.85 5.85 5.85 5.85 242 20090106 ABW 20090107 5.91 5.91 5.91 5.91 2000 20090107 ABW 20090108 5.85 5.85 5.85 5.85 2380 20090108 ABW 20090109 5.8 5.8 5.8 5.8 3468 20090109 ABW 20090112 5.9 5.97 5.9 5.97 4696 20090112 ABW 20090113 5.93 5.93 5.93 5.93 2993 20090113 TIA Tom |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to line up two dates?
Sorry for that unpredictable display. Shall try again below.
Before I can carry out other task, I have to line up the row where Date 1 starts with Date 2 for all other stocks. It is not always 3 rows down as shown below. How would you write the codes to do that? Thanks. Code Date 1 Date 2 ABW 20090106 5.85 5.85 5.85 5.85 242 20090101 ABW 20090107 5.91 5.91 5.91 5.91 2000 20090102 ABW 20090108 5.85 5.85 5.85 5.85 2380 20090105 ABW 20090109 5.8 5.8 5.8 5.8 3468 20090106 ABW 20090112 5.9 5.97 5.9 5.97 4696 20090107 ABW 20090113 5.93 5.93 5.93 5.93 2993 20090108 20090109 20090112 20090113 Code Date 1 Date 2 20090101 20090102 20090105 ABW 20090106 5.85 5.85 5.85 5.85 242 20090106 ABW 20090107 5.91 5.91 5.91 5.91 2000 20090107 ABW 20090108 5.85 5.85 5.85 5.85 2380 20090108 ABW 20090109 5.8 5.8 5.8 5.8 3468 20090109 ABW 20090112 5.9 5.97 5.9 5.97 4696 20090112 ABW 20090113 5.93 5.93 5.93 5.93 2993 20090113 TIA Tom |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to line up two dates?
Select range A2:G4, then InsertCellsShift cells down!
-- Regards! Stefi Tom ezt *rta: Sorry for that unpredictable display. Shall try again below. Before I can carry out other task, I have to line up the row where Date 1 starts with Date 2 for all other stocks. It is not always 3 rows down as shown below. How would you write the codes to do that? Thanks. Code Date 1 Date 2 ABW 20090106 5.85 5.85 5.85 5.85 242 20090101 ABW 20090107 5.91 5.91 5.91 5.91 2000 20090102 ABW 20090108 5.85 5.85 5.85 5.85 2380 20090105 ABW 20090109 5.8 5.8 5.8 5.8 3468 20090106 ABW 20090112 5.9 5.97 5.9 5.97 4696 20090107 ABW 20090113 5.93 5.93 5.93 5.93 2993 20090108 20090109 20090112 20090113 Code Date 1 Date 2 20090101 20090102 20090105 ABW 20090106 5.85 5.85 5.85 5.85 242 20090106 ABW 20090107 5.91 5.91 5.91 5.91 2000 20090107 ABW 20090108 5.85 5.85 5.85 5.85 2380 20090108 ABW 20090109 5.8 5.8 5.8 5.8 3468 20090109 ABW 20090112 5.9 5.97 5.9 5.97 4696 20090112 ABW 20090113 5.93 5.93 5.93 5.93 2993 20090113 TIA Tom . |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to line up two dates?
I'm looking for an automated process, not visual, whereby the first date on
column 2 lines up with its counterpart on column G which can be several rows down. This program needs to be able to read the content of A2, and determine its row position, finds its counterpart and its row position. Now, knowing the row difference, then move A2:G2 down that number of rows. "Stefi" wrote in message ... Select range A2:G4, then InsertCellsShift cells down! -- Regards! Stefi "Tom" ezt rta: Sorry for that unpredictable display. Shall try again below. Before I can carry out other task, I have to line up the row where Date 1 starts with Date 2 for all other stocks. It is not always 3 rows down as shown below. How would you write the codes to do that? Thanks. Code Date 1 Date 2 ABW 20090106 5.85 5.85 5.85 5.85 242 20090101 ABW 20090107 5.91 5.91 5.91 5.91 2000 20090102 ABW 20090108 5.85 5.85 5.85 5.85 2380 20090105 ABW 20090109 5.8 5.8 5.8 5.8 3468 20090106 ABW 20090112 5.9 5.97 5.9 5.97 4696 20090107 ABW 20090113 5.93 5.93 5.93 5.93 2993 20090108 20090109 20090112 20090113 Code Date 1 Date 2 20090101 20090102 20090105 ABW 20090106 5.85 5.85 5.85 5.85 242 20090106 ABW 20090107 5.91 5.91 5.91 5.91 2000 20090107 ABW 20090108 5.85 5.85 5.85 5.85 2380 20090108 ABW 20090109 5.8 5.8 5.8 5.8 3468 20090109 ABW 20090112 5.9 5.97 5.9 5.97 4696 20090112 ABW 20090113 5.93 5.93 5.93 5.93 2993 20090113 TIA Tom . |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to line up two dates?
You should have specified exactly your request! This one line macro does the
job: Sub lineup() Range("A2:G" & Columns("H").Find(Range("B2")).Row - 1).Insert Shift:=xlDown End Sub -- Regards! Stefi Tom ezt *rta: I'm looking for an automated process, not visual, whereby the first date on column 2 lines up with its counterpart on column G which can be several rows down. This program needs to be able to read the content of A2, and determine its row position, finds its counterpart and its row position. Now, knowing the row difference, then move A2:G2 down that number of rows. "Stefi" wrote in message ... Select range A2:G4, then InsertCellsShift cells down! -- Regards! Stefi "Tom" ezt *rta: Sorry for that unpredictable display. Shall try again below. Before I can carry out other task, I have to line up the row where Date 1 starts with Date 2 for all other stocks. It is not always 3 rows down as shown below. How would you write the codes to do that? Thanks. Code Date 1 Date 2 ABW 20090106 5.85 5.85 5.85 5.85 242 20090101 ABW 20090107 5.91 5.91 5.91 5.91 2000 20090102 ABW 20090108 5.85 5.85 5.85 5.85 2380 20090105 ABW 20090109 5.8 5.8 5.8 5.8 3468 20090106 ABW 20090112 5.9 5.97 5.9 5.97 4696 20090107 ABW 20090113 5.93 5.93 5.93 5.93 2993 20090108 20090109 20090112 20090113 Code Date 1 Date 2 20090101 20090102 20090105 ABW 20090106 5.85 5.85 5.85 5.85 242 20090106 ABW 20090107 5.91 5.91 5.91 5.91 2000 20090107 ABW 20090108 5.85 5.85 5.85 5.85 2380 20090108 ABW 20090109 5.8 5.8 5.8 5.8 3468 20090109 ABW 20090112 5.9 5.97 5.9 5.97 4696 20090112 ABW 20090113 5.93 5.93 5.93 5.93 2993 20090113 TIA Tom . . |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to line up two dates?
Works great. Thank you Stefi.
"Stefi" wrote in message ... You should have specified exactly your request! This one line macro does the job: Sub lineup() Range("A2:G" & Columns("H").Find(Range("B2")).Row - 1).Insert Shift:=xlDown End Sub -- Regards! Stefi "Tom" ezt rta: I'm looking for an automated process, not visual, whereby the first date on column 2 lines up with its counterpart on column G which can be several rows down. This program needs to be able to read the content of A2, and determine its row position, finds its counterpart and its row position. Now, knowing the row difference, then move A2:G2 down that number of rows. "Stefi" wrote in message ... Select range A2:G4, then InsertCellsShift cells down! -- Regards! Stefi "Tom" ezt rta: Sorry for that unpredictable display. Shall try again below. Before I can carry out other task, I have to line up the row where Date 1 starts with Date 2 for all other stocks. It is not always 3 rows down as shown below. How would you write the codes to do that? Thanks. Code Date 1 Date 2 ABW 20090106 5.85 5.85 5.85 5.85 242 20090101 ABW 20090107 5.91 5.91 5.91 5.91 2000 20090102 ABW 20090108 5.85 5.85 5.85 5.85 2380 20090105 ABW 20090109 5.8 5.8 5.8 5.8 3468 20090106 ABW 20090112 5.9 5.97 5.9 5.97 4696 20090107 ABW 20090113 5.93 5.93 5.93 5.93 2993 20090108 20090109 20090112 20090113 Code Date 1 Date 2 20090101 20090102 20090105 ABW 20090106 5.85 5.85 5.85 5.85 242 20090106 ABW 20090107 5.91 5.91 5.91 5.91 2000 20090107 ABW 20090108 5.85 5.85 5.85 5.85 2380 20090108 ABW 20090109 5.8 5.8 5.8 5.8 3468 20090109 ABW 20090112 5.9 5.97 5.9 5.97 4696 20090112 ABW 20090113 5.93 5.93 5.93 5.93 2993 20090113 TIA Tom . . |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to line up two dates?
Just found a problem. If the two dates are already aligned, the program also
shift it down two rows which it shouldn't have done. May be it should check for alignment first. "Stefi" wrote in message ... You should have specified exactly your request! This one line macro does the job: Sub lineup() Range("A2:G" & Columns("H").Find(Range("B2")).Row - 1).Insert Shift:=xlDown End Sub -- Regards! Stefi "Tom" ezt rta: I'm looking for an automated process, not visual, whereby the first date on column 2 lines up with its counterpart on column G which can be several rows down. This program needs to be able to read the content of A2, and determine its row position, finds its counterpart and its row position. Now, knowing the row difference, then move A2:G2 down that number of rows. "Stefi" wrote in message ... Select range A2:G4, then InsertCellsShift cells down! -- Regards! Stefi "Tom" ezt rta: Sorry for that unpredictable display. Shall try again below. Before I can carry out other task, I have to line up the row where Date 1 starts with Date 2 for all other stocks. It is not always 3 rows down as shown below. How would you write the codes to do that? Thanks. Code Date 1 Date 2 ABW 20090106 5.85 5.85 5.85 5.85 242 20090101 ABW 20090107 5.91 5.91 5.91 5.91 2000 20090102 ABW 20090108 5.85 5.85 5.85 5.85 2380 20090105 ABW 20090109 5.8 5.8 5.8 5.8 3468 20090106 ABW 20090112 5.9 5.97 5.9 5.97 4696 20090107 ABW 20090113 5.93 5.93 5.93 5.93 2993 20090108 20090109 20090112 20090113 Code Date 1 Date 2 20090101 20090102 20090105 ABW 20090106 5.85 5.85 5.85 5.85 242 20090106 ABW 20090107 5.91 5.91 5.91 5.91 2000 20090107 ABW 20090108 5.85 5.85 5.85 5.85 2380 20090108 ABW 20090109 5.8 5.8 5.8 5.8 3468 20090109 ABW 20090112 5.9 5.97 5.9 5.97 4696 20090112 ABW 20090113 5.93 5.93 5.93 5.93 2993 20090113 TIA Tom . . |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to line up two dates?
Sub lineup()
rowshift = Columns("H").Find(Range("B2")).Row - 1 If rowshift 1 Then Range("A2:G" & rowshift).Insert Shift:=xlDown End Sub Clicking the YES button will be appreciated. -- Regards! Stefi Tom ezt *rta: Just found a problem. If the two dates are already aligned, the program also shift it down two rows which it shouldn't have done. May be it should check for alignment first. "Stefi" wrote in message ... You should have specified exactly your request! This one line macro does the job: Sub lineup() Range("A2:G" & Columns("H").Find(Range("B2")).Row - 1).Insert Shift:=xlDown End Sub -- Regards! Stefi "Tom" ezt *rta: I'm looking for an automated process, not visual, whereby the first date on column 2 lines up with its counterpart on column G which can be several rows down. This program needs to be able to read the content of A2, and determine its row position, finds its counterpart and its row position. Now, knowing the row difference, then move A2:G2 down that number of rows. "Stefi" wrote in message ... Select range A2:G4, then InsertCellsShift cells down! -- Regards! Stefi "Tom" ezt *rta: Sorry for that unpredictable display. Shall try again below. Before I can carry out other task, I have to line up the row where Date 1 starts with Date 2 for all other stocks. It is not always 3 rows down as shown below. How would you write the codes to do that? Thanks. Code Date 1 Date 2 ABW 20090106 5.85 5.85 5.85 5.85 242 20090101 ABW 20090107 5.91 5.91 5.91 5.91 2000 20090102 ABW 20090108 5.85 5.85 5.85 5.85 2380 20090105 ABW 20090109 5.8 5.8 5.8 5.8 3468 20090106 ABW 20090112 5.9 5.97 5.9 5.97 4696 20090107 ABW 20090113 5.93 5.93 5.93 5.93 2993 20090108 20090109 20090112 20090113 Code Date 1 Date 2 20090101 20090102 20090105 ABW 20090106 5.85 5.85 5.85 5.85 242 20090106 ABW 20090107 5.91 5.91 5.91 5.91 2000 20090107 ABW 20090108 5.85 5.85 5.85 5.85 2380 20090108 ABW 20090109 5.8 5.8 5.8 5.8 3468 20090109 ABW 20090112 5.9 5.97 5.9 5.97 4696 20090112 ABW 20090113 5.93 5.93 5.93 5.93 2993 20090113 TIA Tom . . . |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to line up two dates?
Now, that works perfectly. Thanks again Stefi.
I'm accessing this newsgroup using Outlook Express and I can't see any Yes button in it to click. Which website are you posting this Stefi? Regards, Tom "Stefi" wrote in message ... Sub lineup() rowshift = Columns("H").Find(Range("B2")).Row - 1 If rowshift 1 Then Range("A2:G" & rowshift).Insert Shift:=xlDown End Sub Clicking the YES button will be appreciated. -- Regards! Stefi "Tom" ezt rta: Just found a problem. If the two dates are already aligned, the program also shift it down two rows which it shouldn't have done. May be it should check for alignment first. "Stefi" wrote in message ... You should have specified exactly your request! This one line macro does the job: Sub lineup() Range("A2:G" & Columns("H").Find(Range("B2")).Row - 1).Insert Shift:=xlDown End Sub -- Regards! Stefi "Tom" ezt rta: I'm looking for an automated process, not visual, whereby the first date on column 2 lines up with its counterpart on column G which can be several rows down. This program needs to be able to read the content of A2, and determine its row position, finds its counterpart and its row position. Now, knowing the row difference, then move A2:G2 down that number of rows. "Stefi" wrote in message ... Select range A2:G4, then InsertCellsShift cells down! -- Regards! Stefi "Tom" ezt rta: Sorry for that unpredictable display. Shall try again below. Before I can carry out other task, I have to line up the row where Date 1 starts with Date 2 for all other stocks. It is not always 3 rows down as shown below. How would you write the codes to do that? Thanks. Code Date 1 Date 2 ABW 20090106 5.85 5.85 5.85 5.85 242 20090101 ABW 20090107 5.91 5.91 5.91 5.91 2000 20090102 ABW 20090108 5.85 5.85 5.85 5.85 2380 20090105 ABW 20090109 5.8 5.8 5.8 5.8 3468 20090106 ABW 20090112 5.9 5.97 5.9 5.97 4696 20090107 ABW 20090113 5.93 5.93 5.93 5.93 2993 20090108 20090109 20090112 20090113 Code Date 1 Date 2 20090101 20090102 20090105 ABW 20090106 5.85 5.85 5.85 5.85 242 20090106 ABW 20090107 5.91 5.91 5.91 5.91 2000 20090107 ABW 20090108 5.85 5.85 5.85 5.85 2380 20090108 ABW 20090109 5.8 5.8 5.8 5.8 3468 20090109 ABW 20090112 5.9 5.97 5.9 5.97 4696 20090112 ABW 20090113 5.93 5.93 5.93 5.93 2993 20090113 TIA Tom . . . |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to line up two dates?
Never mind, Tom, "Yes" is only in MS Office online site.
-- Regards! Stefi Tom ezt *rta: Now, that works perfectly. Thanks again Stefi. I'm accessing this newsgroup using Outlook Express and I can't see any Yes button in it to click. Which website are you posting this Stefi? Regards, Tom "Stefi" wrote in message ... Sub lineup() rowshift = Columns("H").Find(Range("B2")).Row - 1 If rowshift 1 Then Range("A2:G" & rowshift).Insert Shift:=xlDown End Sub Clicking the YES button will be appreciated. -- Regards! Stefi "Tom" ezt *rta: Just found a problem. If the two dates are already aligned, the program also shift it down two rows which it shouldn't have done. May be it should check for alignment first. "Stefi" wrote in message ... You should have specified exactly your request! This one line macro does the job: Sub lineup() Range("A2:G" & Columns("H").Find(Range("B2")).Row - 1).Insert Shift:=xlDown End Sub -- Regards! Stefi "Tom" ezt *rta: I'm looking for an automated process, not visual, whereby the first date on column 2 lines up with its counterpart on column G which can be several rows down. This program needs to be able to read the content of A2, and determine its row position, finds its counterpart and its row position. Now, knowing the row difference, then move A2:G2 down that number of rows. "Stefi" wrote in message ... Select range A2:G4, then InsertCellsShift cells down! -- Regards! Stefi "Tom" ezt *rta: Sorry for that unpredictable display. Shall try again below. Before I can carry out other task, I have to line up the row where Date 1 starts with Date 2 for all other stocks. It is not always 3 rows down as shown below. How would you write the codes to do that? Thanks. Code Date 1 Date 2 ABW 20090106 5.85 5.85 5.85 5.85 242 20090101 ABW 20090107 5.91 5.91 5.91 5.91 2000 20090102 ABW 20090108 5.85 5.85 5.85 5.85 2380 20090105 ABW 20090109 5.8 5.8 5.8 5.8 3468 20090106 ABW 20090112 5.9 5.97 5.9 5.97 4696 20090107 ABW 20090113 5.93 5.93 5.93 5.93 2993 20090108 20090109 20090112 20090113 Code Date 1 Date 2 20090101 20090102 20090105 ABW 20090106 5.85 5.85 5.85 5.85 242 20090106 ABW 20090107 5.91 5.91 5.91 5.91 2000 20090107 ABW 20090108 5.85 5.85 5.85 5.85 2380 20090108 ABW 20090109 5.8 5.8 5.8 5.8 3468 20090109 ABW 20090112 5.9 5.97 5.9 5.97 4696 20090112 ABW 20090113 5.93 5.93 5.93 5.93 2993 20090113 TIA Tom . . . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Line Chart for Dates | Charts and Charting in Excel | |||
| remove dates is the middle of a line | Excel Discussion (Misc queries) | |||
| Column-Line Chart w/ Dates | Charts and Charting in Excel | |||
| multi-line graph with dates on x-axis | Charts and Charting in Excel | |||
| Dates to number in edit line | Excel Worksheet Functions | |||
Linear Mode
