ACCESS FEED: RE: [MS_AccessPros] Excel Columns

Minggu, 09 Februari 2014

RE: [MS_AccessPros] Excel Columns

You an do away with most of that looping

'assuming your table may have text or number

Sub transposee()

Dim lrow As Long, i As Long, z As Long

Columns("W:W").ClearContents

lrow = Cells(Rows.Count, "A").End(xlUp).Row

z = 2

For i = 2 To 16 ' Here assumed that column A is not having data

Cells(z, 23).Resize(lrow).Value = Cells(1, i)

Cells(2, i).Resize(lrow).Copy Cells(z, 24)

z = z + lrow

End Sub

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Ty Bak
Sent: 08 February 2014 23:00
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Excel Columns

I got an answer but I need the data in 2 different cells or columns. This will put the abc123 together but I need column 1 = abc and column 2 = 123.

Option Explicit
'assuming your table may have text or number
Sub transposee()

Dim lrow As Integer, i As Integer, t As Integer, z As Long
Dim sHead As String, sVal As String

Columns("W:W").ClearContents
lrow = Cells(Rows.Count, "A").End(xlUp).Row
z = 2
   For i = 2 To 16 ' Here assumed that column A is not having data
      sHead = Cells(1, i)
         For t = 2 To lrow
            sVal = Cells(t, i)
              Cells(z, 23).Value = sHead & sVal ' values will appear in col W
              z = z + 1
         Next

   Next
End Sub

Thanks,

On Saturday, February 8, 2014 3:46 PM, John Viescas <JohnV@msn.com> wrote:

Ty-

In a database, I'd do it with a UNION query:

SELECT "abc", abc
FROM MyTable
UNION ALL
SELECT "def", def
FROM MyTable
…

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Feb 8, 2014, at 9:55 PM, tyrone_bak <tyrone_bak@yahoo.com> wrote:

> I have a problem with data in a MS Excel spreadsheet in a column. I need 15 columns converted to rows. On the column I need the header to repeat for every item within that column.
>
> problem:
> column abc
> row 222
> row 333
> row 444
> row 555
> row 1236
> row 2bca
>
> solution needed:
> col-1/col-2 as headers rows all down below
> abc 222
> abc 333
> abc 444
> abc 555
> abc 1236
> abc 2bca
>
> Any help will be greatly appreciated,
> Ty