VBA to change column to row and store the result in new column

[abraham30]

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

[ashleyuk1984]

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 :-)

[abraham30]

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.

[ashleyuk1984]

Sorry, you misunderstood, I meant MY CODE was ugly :) haha

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

[Bob Phillips]

Why don't you just use formulae?

=SUM(Data!G2:J2)
and
=SUM(Data!K2:N2)

and copy them down.