transpose column to a range calculate and transpose to new table loop

**moshro1** · 12-31-2014, 09:51 AM

hi

This must be basic but i'm drawing a blank. i have a table with data that i need to transpose each coumn into a row that calculates the results and then to transpose the results to a new table. also it would be preferable that if the sum of column "n" is "0" the loop sould stop and fill the next columns "n+1..." of the new table with 0's. attatched is a sample of the file.

**Kaper** · 12-31-2014, 01:30 PM

Try this simple code:

Sub test()
Dim i As Long
For i = 2 To 7
  Cells(17, 2).Resize(1, 10).Value = Application.WorksheetFunction.Transpose(Cells(4, i).Resize(10, 1))
  Application.Calculate
  Cells(4, i + 8).Resize(10, 1).Value = Application.WorksheetFunction.Transpose(Range("B18:K18"))
Next i
End Sub

**moshro1** · 12-31-2014, 02:43 PM

kaper thanks for the reply, the macro wont work becouse i should have clarified that i need the transposed numbers to be a formula becouse they feed a what if data table and if they would be hard cosded i wont get any new simulations. is there a way to get the columns transposed but keeping the columns as a reference?

**Kaper** · 01-02-2015, 10:52 AM

Well, I probably do not really follow your idea, but let's try another approach:

Sub test2()
Dim i As Long, j As Long
Range("J4:O13").ClearContents
For i = 2 To 7
  For j = 4 To 13
    Cells(j, i + 8).Formula = "=" & Cells(j, i).Address & Mid(Cells(18, j - 2).Formula, InStr(Cells(18, j - 2).Formula, "*"))
Next j, i
End Sub

transpose column to a range calculate and transpose to new table loop

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

transpose column to a range calculate and transpose to new table loop

Re: transpose column to a range calculate and transpose to new table loop

Re: transpose column to a range calculate and transpose to new table loop

Re: transpose column to a range calculate and transpose to new table loop

Thread Information

Users Browsing this Thread

Similar Threads

Transpose Data from a Particular Column to Multiple Column whose Range is Non-Continuous!

transpose column in to table format

how to transpose row data into column wise into new table in access

Transpose range into table

[SOLVED] transpose a range to a single column

Bookmarks

Bookmarks

Posting Permissions