+ Reply to Thread
Results 1 to 3 of 3

converting "matrix" of values into single column

  1. #1
    Registered User
    Join Date
    06-27-2007
    Posts
    2

    converting "matrix" of values into single column

    alright, first i have to say that i have been happyly taking advantage of this website all summer. it has really help me to write some pretty awesome macros.

    problem i have having now is converting a "matrix" of values into a single column on another sheet. here is an example of the data.

    Data Sheet - worksheet
    Col S Col T Col U Col V
    Row 1 85828 86217 87170 0
    Row 2 84648 0 0 0
    Row 3 86671 86750 87203 0
    Row 4 83205 86640 0 0
    Row 5 86483 0 0 0
    Row 6 86611 0 0 0
    Row 7 86375 86425 0 0

    i have no problem in deleting the "0" i don't want. just having some trouble with the marco "for statement". here is what i got.

    For ik = 1 To 7
    rp = 18
    Cells(ik + 3, 1).Select
    ActiveCell.FormulaR1C1 = "='Data Sheet'!R[ik]C[rp]"
    ik = ik + 1
    rp = rp + 1
    Cells(ik + 3, 1) = "='Data Sheet'!R[ik]C[rp]"
    ik = ik + 1
    rp = rp + 1
    Cells(ik + 3, 1) = "='Data Sheet'!R[ik]C[rp]"
    ik = ik + 1
    rp = rp + 1
    Cells(ik + 3, 1) = "='Data Sheet'!R[ik]C[rp]"
    ik = ik + 1
    rp = rp + 1
    Next ik

    one counter is for the rows and one is cor the columns. the biggest problem is with the reference of the "Data Sheet" page. i assume that it is just a problem with format. sorry if this is a little long winded. just hoping someone can shead some light for me. if there is anyway i can help describe the problem anymore just tell me. thanks.

  2. #2
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,206
    This will put your matrix into column A.
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    06-27-2007
    Posts
    2
    thanks mike. that was helpful. i was able to mod your code to fit my needs. the only thing that gave me a fuss was there needs to be "Set" at the beginning of the 5th line of code. with that fixed it was working great. thanks again.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1