Simple (I think) problem

[hallingh]

Hi all. I have a sheet that is archiving data and graphing it by quarter (First quarter of 2011, second quarter of 2011, etc.) My issue is, that in order for my graph to work correctly I need to have a column that contains the quarter in the format "1Q 2011." However, the user simply selectes from a drop down what quarter it is, and the choices are: "1st Quarter" "2nd Quarter" "3rd Quarter" and "4th Quarter". I'm using a macro to import the data, but currently I'm just importing all of the numerical data along with the year. This is the macro:

Sub ClearDoc()
ArchiveRows = (Sheets("Data Archive").Range("E" & Rows.Count).End(xlUp).Row) + 1
    Sheets("Data Archive").Range("A" & ArchiveRows).Value = ActiveSheet.Range("B" & (Q - 1) & ":B" & Q).Value
    Sheets("Data Archive").Range("B" & ArchiveRows).Value = ActiveSheet.Range("C" & (Q - 1)).Value
    Sheets("Data Archive").Range("C" & ArchiveRows).Value = ActiveSheet.Range("E" & (Q - 1)).Value
    Sheets("Data Archive").Range("D" & ArchiveRows).Value = [B]ActiveSheet.Name[/B]
[B]Sheets("Data Archive").Range("E" & ArchiveRows).Value = Year(Int(NOW())[/B]
    
 
 
End Sub

This highlighted code is what gives me the year, but I now need it to give me the Quarter and the year. The quarter is in the form "1st Quarter" in the cell:

ActiveSheet.Range("AM" & (Q-1))

Thanks for any help you can give me!

Hank

 

[hallingh]

Oh, forgot to say that Q and ArchiveRows are dimmed as Long in the actual sub. I'm sure you assumed that was the case.

Thanks!