Results 1 to 5 of 5

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

  1. #1

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

    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
    Attached Files Attached Files

  2. #2
    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 :-)

  3. #3
    Thanks ashley 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.

  4. #4
    Sorry, you misunderstood, I meant MY CODE was ugly haha

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

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Apr 2005
    Why don't you just use formulae?


    and copy them down.
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts