Consulting

Results 1 to 13 of 13

Thread: Solved: linking to a range of cells

  1. #1

    Solved: linking to a range of cells

    The solution to this may or may not be VBA but I'll post it anyway in the hope that I can avert the mind-deadening tedium:
    1. I have multiple rows with 27 columns of Sales by product, running horizontally across the page.
    2. For a Pivot table I need to link to these entire ranges, however the data for the Pivot table has to run vertically, in a single column, i.e. it needs to be transposed.
    3. I tried to do this by naming the ranges but I may be doing it incorrectly because I get a #VALUE! error
    It would have been easy enough if I simply had to transpose and copy the data however in this case I need to have live links. Is there a way to do this in an "automated" fashion without having to link to each cell individually by pointing and clicking?

    Many thanks!

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    [vba]

    Public Sub ProcessData()
    Dim LastRow As Long
    Dim LastCol As Long
    Dim i As Long, j As Long

    With ActiveSheet

    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    For i = LastRow To 2 Step -1

    For j = LastCol To 2 Step -1

    If .Cells(i, j).Value2 <> "" Then

    .Rows(i + 1).Insert
    .Cells(i, "A").Copy .Cells(i + 1, "A")
    .Cells(1, j).Copy .Cells(i + 1, "B")
    .Cells(i, j).Copy .Cells(i + 1, "C")
    End If
    Next j

    .Rows(i).Delete
    Next i
    End With

    End Sub
    [/vba]
    ____________________________________________
    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

  3. #3
    I have 24 rows with 27 columns to transpose and link to. Do I have to run the macro 24 times (once for each row)? Also the transposed data is on a different sheet than the original data.

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    No just once.

    Copy it to a new sheet and run against that sheet.
    ____________________________________________
    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

  5. #5
    The commands read "copy .cells," will this actually give me data links or just copy the data?

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    It will restructue the actual data.
    ____________________________________________
    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

  7. #7
    Unfortunately it does not work for me. Perhaps I should have highlighted the fact that the rows that need to be transposed and linked to are not part of a contiguous data table. I am dealing of blocks of data and the rows in question occur at an interval of 6 (e.g. the first incidence is Row 9, the next is Row 15, the next Row 21, and so on). Thanks for trying to help.

  8. #8
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    I made up this to create a linked transposed table. Any use?
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  9. #9
    Quote Originally Posted by mdmackillop
    I made up this to create a linked transposed table. Any use?
    Extremely interesting, thanks. However, I am trying to do the reverse, i.e. take data arranged horizontally, across a row, and link to it creating a single column of data in. Also would this work if the row of data and the target column were in different worksheets of the same workbook?

  10. #10
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by K. Georgiadis
    Unfortunately it does not work for me. Perhaps I should have highlighted the fact that the rows that need to be transposed and linked to are not part of a contiguous data table. I am dealing of blocks of data and the rows in question occur at an interval of 6 (e.g. the first incidence is Row 9, the next is Row 15, the next Row 21, and so on). Thanks for trying to help.
    Why don't you post the workbook.
    ____________________________________________
    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

  11. #11
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Quote Originally Posted by K. Georgiadis
    Extremely interesting, thanks. However, I am trying to do the reverse, i.e. take data arranged horizontally, across a row, and link to it creating a single column of data in. Also would this work if the row of data and the target column were in different worksheets of the same workbook?
    Yes
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  12. #12
    Quote Originally Posted by mdmackillop
    Yes
    It works like a charm! Many thanks!

    Marked as "solved."
    Last edited by K. Georgiadis; 03-23-2010 at 11:51 AM.

  13. #13
    Quote Originally Posted by xld
    Why don't you post the workbook.
    It is too large a file and contains proprietary information. Meanwhile MdMcKillop's suggestion has solved the problem completely. Thanks again.

Posting Permissions

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