View Full Version : VBA to change column to row and store the result in new column

02-22-2014, 12:33 PM
Hello Everyone!

In the attached sheet, I want to copy the result of columns G-J from sheet "Data" and store it in newly created column (Suppose "parameter") in sheet "Result" and its value in column "Value".

Same process should be done for col K-N from sheet "Data" and store the data in newly created col "VarCategory" and value in same column "Value".

Both rows and columns are present dynamically

Thanks in advance

02-22-2014, 01:19 PM
This is a very ugly way and long way of doing the task... BUT, I tried to make it easy for you to understand.
Hope this helps.

Sub calcu()
Dim LastRow As Integer
Dim TotalofGtoJ as Interger
Dim TotalofKtoN as Interger

'Sheets(1) = DATA Sheet
'Sheets(2) = Result Sheet
'Could make this easier by declaring sheets with variables, but lets keep it simple :)

'Find Last Row of data in DATA sheet and store the ROW number as LastRow

LastRow = Sheets(1).Range("A9999").End(xlUp).Row

'x = 2, because we don't want to include your headers

For x = 2 To LastRow

'TotalofGtoJ is going to be the calculation of the four columns - same goes for TotalofKtoN
'There are LOADS of different ways to calculate columns, I will just use this way to make it easier to read

TotalofGtoJ = Sheets(1).Range("G" & x).Value + Sheets(1).Range("H" & x).Value + Sheets(1).Range("I" & x).Value + Sheets(1).Range("J" & x).Value
TotalofKtoN = Sheets(1).Range("K" & x).Value + Sheets(1).Range("L" & x).Value + Sheets(1).Range("M" & x).Value + Sheets(1).Range("N" & x).Value 'Add "+ Sheets(1).Range("O" & x).Value" if you want column O as well.

'I presume the number of columns will be exactly the same on the Data sheet as the Result sheet... So therefore, I will just use the value of x on both sheets.

Sheets(2).Range("I" & x).Value = TotalofGtoJ
Sheets(2).Range("H" & x).Value = TotalofKtoN
Next x

End Sub

You said, "Same process should be done for col K-N" ... But I saw that your spreadsheet 'Errors' columns actually go up to column O, but I done the code as requested. As it's quite simple to understand, you should be able to add column O if required by yourself :-)

02-22-2014, 01:31 PM
Thanks ashle (http://www.vbaexpress.com/forum/member.php?49500-ashleyuk1984)y for your quick prompt. I know this is an ugly way of representing data. But I have no other option which I got from my client.

While running the macro, I am getting compilation error as User-defined type not defined.
Could you plz attache the sheet.
If possible, could you plz add the column for last criteria as I have not that much idea about macro process.

02-22-2014, 01:50 PM
Sorry, you misunderstood, I meant MY CODE was ugly :) haha

Sorry, it was a spelling mistake on my part.
Here you go.

Bob Phillips
02-22-2014, 04:32 PM
Why don't you just use formulae?


and copy them down.