Transposing Formulas

[Joe Gieder]

Is there a way to convert the formulas that go
horizontaly to verticaly. I have these formulas under
these columns:

A B C D E F G H I J K L..
Ship Cost NRE Ship Cost NRE Ship Cost NRE Ship Cost NRE
1 1.00 0 2 5.00 2 3 6.25 3 4 8.00 0

the above values have the "SUM" formula for the coulmns.
The result I'm trying to get is:

Ship Cost NRE
1 1.00 0.00
2 5.00 2.00
3 6.25 3.00
4 8.00 0.00
Thru
1125

can this be done easily
thanks in advance for your help.

Joe

 

[Bernie Deitrick]

Joe,

Given the 256 column limitation, you could only get 85 or so records. Where
are you getting 1125? That will impact the solution.

HTH,
Bernie
MS Excel MVP

 

[Joe Gieder]

Berie,
I go across through 75 ships at a time then skip down
to the next row. My data is repetitive since I have to
show a curve based on the values of 1 thru 1125.

Joe

 

[Bernie Deitrick]

Joe,

Try the sub below. Written based on the assumption that your table starts in
Cell A1, is contiguous, is a multiple of 3 columns wide, and has labels in
the first row.

HTH,
Bernie
MS Excel MVP

Sub JoeTranspose()
Dim i As Integer
Dim myRange As Range
Dim mySht As Worksheet

Set myRange = Range("A1").CurrentRegion.Offset(1)

Set mySht = Worksheets.Add
mySht.Name = "New Table"
mySht.Range("A1").Value = "Ship"
mySht.Range("B1").Value = "Cost"
mySht.Range("C1").Value = "NRE"

For i = 1 To myRange.Cells.Count Step 3
mySht.Range("A65536").End(xlUp)(2).Value = myRange.Cells(i).Value
mySht.Range("B65536").End(xlUp)(2).Value = myRange.Cells(i + 1).Value
mySht.Range("C65536").End(xlUp)(2).Value = myRange.Cells(i + 2).Value
Next i

End Sub