One way:
Option Explicit
Sub testme01()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iCol As Long
Dim FirstCol As Long
Dim LastCol As Long
Dim wks As Worksheet
Dim myStr As String
Set wks = Worksheets("sheet1")
Close #1 'just in case it's open
Open "C:\Temp\Output.CSV" For Output As #1
With wks
FirstRow = 1
FirstCol = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
For iRow = FirstRow + 1 To LastRow
For iCol = FirstCol + 1 To LastCol
myStr = .Cells(1, iCol).Text & "," _
& .Cells(iRow, "A").Text & "," _
& .Cells(iRow, iCol).Text
Print #1, myStr
Next iCol
Next iRow
End With
Close #1
End Sub
Joe K. wrote:
>
> I have a spreadsheet with the current worksheet that has data in the format
> listed below. The dates are in row1 and the state abbreviation are listed in
> Column A.
> The values for each states are listed on each row.
>
> Please help me create a macro that will take the worksheet values and output
> to a
> CSV file in the desired output listed below.
>
> Thank you very much.
>
> Spreadsheet
> 5/26/07 5/27/07 5/28/07 ... 08/30/07
> NM 56.0 68.0 72.0 64.0
> TX 23.0 28.0 34.0 16.0
> NY 16.0 18.0 22.0 32.0
>
> Desired Output
>
> 5/26/07,NM,56.0
> 5/27/07,NM,68.0
> 5/28/07,NM,72.0
> ...
> 8/30/07,NM,64.0
> 5/26/07,TX,23.0
> 5/27/07,TX,28.0
> 5/28/07,TX,24.0
> ...
> 8/30/07,TX,16.0
> 5/26/07,NY,16.0
> 5/27/07,NY,18.0
> 5/28/07,NY,22.0
> ...
> 08/30/07,NY,32.0
--
Dave Peterson
|
Similar Threads
