›

Display data from rows to column using VBA

b0ssb0ss
in VBA
Hi i would really need some help here with VBA. I am quite new to programming. I have some data in excel that is display in rows. I want them to display in column instead. Any idea how i can do it? i did some coding but it only display the first line of code.

Initial Result:
John S1 Math 1
John S1 English 2
John S1 Chinese 3
Benny S2 Sci C
Benny S2 Hist D
Benny S2 Geo E
Ken S3 Bio C
Ken S3 Chem C
Ken S3 Phy A
Joyce S4 Lit A
Joyce S4 French E
Joyce S4 German D

Expected result:
John S1 Math A English B Chinese C
Benny S2 Math C English D Geo D
Ken S3 Bio C Chem C Phy A
Joyce S4 Lit A French E German D

How can i go abt doing dis? my coding looks like this:
Private Sub CommandButton1_Click()
x = 1
n = 1
i = 1

For x = 1 To 1
Sheet2.Cells(n, 1).Value = Sheet1.Cells(2, i)
Sheet2.Cells(n, 2).Value = Sheet1.Cells(2, (i + 1))
Sheet2.Cells(n, 3).Value = Sheet1.Cells(2, (i + 2))
Sheet2.Cells(n, 4).Value = Sheet1.Cells(2, (i + 3))
Sheet2.Cells(n, 5).Value = Sheet1.Cells((2 + x), (i + 2))
Sheet2.Cells(n, 6).Value = Sheet1.Cells((2 + x), (i + 3))
Sheet2.Cells(n, 7).Value = Sheet1.Cells((2 + x + 1), (i + 2))
Sheet2.Cells(n, 8).Value = Sheet1.Cells((2 + x + 1), (i + 3))
x = x + 1
n = n + 1
i = i + 1
Next
End Sub

Comments

  • dokken2dokken2
    see uploaded file, has a VBA macro to switch rows to columns
  • b0ssb0ss
    Hi, Thank you for the reply. The sample code is really useful but for my subject, it is a dyanamic data. I cant dim the subject as the sample code stated else i would like to key in a lot of subject.

    Engl As String
    Math As String
    Hist As String
    Geog As String
    Chem As String

    How can i go about doing that?
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Register

Categories

In this Discussion