| Home | Forums | Reviews | Articles | Register |
 |
| Thread Tools | Rate Thread |
|
Guest
Posts: n/a
|
|
|
|
||
|
|
| |
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
This will work. I had a slight mistake with your previous posting that the
months were off by one month. this code I fully tested. I converted the month year to a Quarter and then did a lookup in column D to find the correct quarter. Sub combineGDP() RowCount = 2 Do While Range("A" & RowCount) <> "" If Range("B" & RowCount) = "" Then MyDate = Range("A" & RowCount) MyYear = Year(MyDate) MyMonth = Month(MyDate) Quarter = Int((MyMonth - 1) / 3) + 1 SearchDate = MyYear & "q" & Quarter Set c = Columns("D  ").Find(what:=SearchDate, _LookIn:=xlValues) If Not c Is Nothing Then Range("B" & RowCount) = c.Offset(0, 1) End If End If RowCount = RowCount + 1 Loop End Sub "(E-Mail Removed)" wrote: > On Nov 9, 4:37 pm, "gaga.kap...@gmail.com" <gaga.kap...@gmail.com> > wrote: > > Hi All, > > This is a snipet of my spreadsheet, this goes one untill 2007...: > > > > A B C D E > > 1 GDP GDP > > 2 Jan-1981 5,307.5 1981q1 5,307.5 > > 3 Feb-1981 5,307.5 1981q2 5,266.1 > > 4 Mar-1981 5,307.5 1981q3 5,329.8 > > 5 Apr-1981 5,266.1 1981q4 5,263.4 > > 6 May-1981 5,266.1 1982q1 5,177.1 > > 7 Jun-1981 5,266.1 1982q2 5,204.9 > > 8 Jul-1981 5,329.8 1982q3 5,185.2 > > 9 Aug-1981 5,329.8 1982q4 5,189.8 > > 10 Sep-1981 5,329.8 1983q1 5,253.8 > > 11 Oct-1981 5,263.4 1983q2 5,372.3 > > 12 Nov-1981 5,263.4 1983q3 5,478.4 > > 13 Dec-1981 5,263.4 1983q4 5,590.5 > > 14 Jan-1982 5,177.1 1984q1 5,699.8 > > 15 Feb-1982 5,177.1 1984q2 5,797.9 > > 16 Mar-1982 5,177.1 1984q3 5,854.3 > > 17 Apr-1982 1984q4 5,902.4 > > 18 May-1982 1985q1 5,956.9 > > 19 Jun-1982 1985q2 6,007.8 > > 20 Jul-1982 1985q3 6,101.7 > > 21 Aug-1982 1985q4 6,148.6 > > 22 Sep-1982 1986q1 6,207.4 > > > > What I want to do is to continue copying values automatically from the > > E column into the B column in the appropriate time frame. > > Q1 = Jan, Feb, Mar > > Q2 = Apr, May, Jun > > Q3 = Jul, Aug, Sep > > Q4 = Oct, Nov, Dec > > > > What code would I use? > > I didnt' come out very well, > Column A refers to the monthly change > Column B is the same as Column E > Column C is blank > Column D are the quarters for each year. > > |
|
|
|
|
|||
|
Guest
Posts: n/a
|
On Nov 10, 6:47 am, Joel <J...@discussions.microsoft.com> wrote:
> This will work. I had a slight mistake with your previous posting that the > months were off by one month. this code I fully tested. I converted the > month year to a Quarter and then did a lookup in column D to find the correct > quarter. > > Sub combineGDP() > > RowCount = 2 > Do While Range("A" & RowCount) <> "" > If Range("B" & RowCount) = "" Then > MyDate = Range("A" & RowCount) > MyYear = Year(MyDate) > MyMonth = Month(MyDate) > Quarter = Int((MyMonth - 1) / 3) + 1 > SearchDate = MyYear & "q" & Quarter > Set c = Columns("D ").Find(what:=SearchDate, _> LookIn:=xlValues) > If Not c Is Nothing Then > Range("B" & RowCount) = c.Offset(0, 1) > End If > End If > RowCount = RowCount + 1 > Loop > > End Sub > > > > "gaga.kap...@gmail.com" wrote: > > On Nov 9, 4:37 pm, "gaga.kap...@gmail.com" <gaga.kap...@gmail.com> > > wrote: > > > Hi All, > > > This is a snipet of my spreadsheet, this goes one untill 2007...: > > > > A B C D E > > > 1 GDP GDP > > > 2 Jan-1981 5,307.5 1981q1 5,307.5 > > > 3 Feb-1981 5,307.5 1981q2 5,266.1 > > > 4 Mar-1981 5,307.5 1981q3 5,329.8 > > > 5 Apr-1981 5,266.1 1981q4 5,263.4 > > > 6 May-1981 5,266.1 1982q1 5,177.1 > > > 7 Jun-1981 5,266.1 1982q2 5,204.9 > > > 8 Jul-1981 5,329.8 1982q3 5,185.2 > > > 9 Aug-1981 5,329.8 1982q4 5,189.8 > > > 10 Sep-1981 5,329.8 1983q1 5,253.8 > > > 11 Oct-1981 5,263.4 1983q2 5,372.3 > > > 12 Nov-1981 5,263.4 1983q3 5,478.4 > > > 13 Dec-1981 5,263.4 1983q4 5,590.5 > > > 14 Jan-1982 5,177.1 1984q1 5,699.8 > > > 15 Feb-1982 5,177.1 1984q2 5,797.9 > > > 16 Mar-1982 5,177.1 1984q3 5,854.3 > > > 17 Apr-1982 1984q4 5,902.4 > > > 18 May-1982 1985q1 5,956.9 > > > 19 Jun-1982 1985q2 6,007.8 > > > 20 Jul-1982 1985q3 6,101.7 > > > 21 Aug-1982 1985q4 6,148.6 > > > 22 Sep-1982 1986q1 6,207.4 > > > > What I want to do is to continue copying values automatically from the > > > E column into the B column in the appropriate time frame. > > > Q1 = Jan, Feb, Mar > > > Q2 = Apr, May, Jun > > > Q3 = Jul, Aug, Sep > > > Q4 = Oct, Nov, Dec > > > > What code would I use? > > > I didnt' come out very well, > > Column A refers to the monthly change > > Column B is the same as Column E > > Column C is blank > > Column D are the quarters for each year.- Hide quoted text - > > - Show quoted text - Thanks, I really appreciate it... |
|
|
|
|
|||
|
|
| |
|
| Thread Tools | |
| Rate This Thread | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| How do I subtract time where hh:mm:ss:ff (frames = 30 frames/sec) | =?Utf-8?B?S0o3?= | Microsoft Excel Misc | 7 | 12th Feb 2010 02:25 PM |
| Counting time-frames | Ken G. | Microsoft Excel Misc | 2 | 12th Aug 2008 01:11 PM |
| 2 Lists but different time frames. | gaga.kaplan@gmail.com | Microsoft Excel Programming | 1 | 9th Nov 2007 11:18 PM |
| Time Out Frames | Lorraine | Windows XP Internet Explorer | 2 | 31st Jan 2004 04:30 PM |
| Re: Specific Time Frames | Jeff Boyce | Microsoft Access Reports | 0 | 18th Oct 2003 02:14 PM |
