ACCESS FEED: RE: [MS_AccessPros] Excel Columns

Senin, 10 Februari 2014

RE: [MS_AccessPros] Excel Columns

Sorry Ty, that line should read

lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Column

BTW, this assumes that row 1 has column headers for each of those columns. If it doesn't, change the 1 in that line to 2 or wherever your data starts.

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

Thanks for your help, Bob, It is stopping on this line "lastcol = .Cells(1, .Columns.Count).End(xlLeft).Column" I am not that good at Excel and Macros. I clicked on cell a1 and ran the macro. Then I changed the name of the worksheet in the macro from Sheet2= ID. The it stopped on the line above. One thing I can say is that column A only have row 2-3 with data. Then another column like F will have 200 rows.

Regards,

On Sunday, February 9, 2014 6:02 PM, Bob Phillips <bob.phillips@dsl.pipex.com> wrote:

Not tested, but this should do what you want

'assuming your table may have text or number

Sub transposee()

Dim target As Worksheet

Dim lastrow As Long

Dim lastcol As Long

Dim i As Long, z As Long

Set target = Worksheets("Sheet2") 'change as appropriate

With ActiveSheet

.Columns("W:W").ClearContents

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

lastcol = .Cells(1, .Columns.Count).End(xlLeft).Column

z = 2

For i = 2 To lastcol

target.Cells(z, "W").Resize(lastrow).Value = .Cells(1, i)

.Cells(2, i).Resize(lastrow).Copy target.Cells(z, "X")

z = z + lastrow

End With

End Sub

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

Thanks! Bob. Bob or Anyone, The number of columns and rows will change week to week. Like 200 rows. Is it possible to have the data placed on a separate sheet? Thanks, Ty

On Sunday, February 9, 2014 5:26 AM, Bob Phillips <bob.phillips@dsl.pipex.com> wrote:

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