Results 1 to 6 of 6
  1. #1
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194

    Need help with Looping

    Hi. I'm unfamiliar with looping and am having trouble understanding the literature. Could someone please help me with this problem:

    I have a command that exports 2 crosstab queries to excel in different spots. My problem is that the 2nd crosstab is out of order (for good reason). There are 10 columns, each with a heading. I need to search range 2 (the 2nd crosstab query) for each heading in range 1 and move a specific dataset to that column in range 1.

    Here is the code I've written. It's only able to search for one value and move it to a specific spot:


    Code:
    Set rng = wks.Range("C" & Lrow + 20, "L" & Lrow + 20)
    For Each Cel In rng
        If Cel.Value = wks.Range("C1") Then
            Cel.Select
            Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(10, 0)).Select
            Selection.Cut _
            Destination:=wks.Range("C" & Lrow1 + 7)
        End If
    Next Cel
    Is there any way to modify this code to loop through range 1 searching range 2 for each heading and moving it to the appropriate column? Otherwise I'll have to paste this code 10 times...

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I am confused. You export 2 CROSSTABs, then you want to overwrite data for one of them?

    Could the need for this be eliminated if the CROSSTAB columns are output in desired order?

    Post sample data and desired output.

    Why export to Excel?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194
    Crosstab 1 distributes to A1:V25 and crosstab 2 distributes to A40:L50. Here is the complicated reason for crosstab 2 being out of order: During the process of exporting crosstab 1 columns are inserted between each column heading to make space for a percentage calculation. Secondly, not all column headings have corresponding information in crosstab 2. This is why crosstab 2 looks out of order; it looks like this:

    Before
    Crosstab 1
    1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 ( "|" represents a column insertion)

    Crosstab 2
    1 2 3 4 5 8 9 10 (notice there is no information for 6 or 7 nor is there a column inserted)

    I'm trying to search crosstab 2 (A40:L50) for each value in crosstab 1. I'd like it to loop through each value in crosstab one headings (A1:V1) and find it in crosstab 2. When it does, I need to move the found value and .offset(10, 0).Select, then cut and paste to the appropriate column.

    After
    Crosstab 1
    1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 ( "|" represents a column insertion)

    Crosstab 2
    1 | 2 | 3 | 4 | 5 | - | - | 8 | 9 | 10

    Now the code I've written gets the job done, but I'd have to write it out 10 times (1 for each value in crosstab 1 column headings). I'd like to write one code for all 10 values.

  4. #4
    Peter M is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2017
    Posts
    67
    I'm a bit slow today and missing something in your code that tells me where it is looking for Column1, but can you configure it to loop like this:

    Code:
    For i = 1 to 10
      sub_routine(i)
    next i
    Where sub_routine(i) is the code that works, with i equal to the Column1 reference?

    e.g.
    sub_routine("1")
    sub_routine("2")

    Code:
    public sub sub_routine(var_ColumnHeading as integer)
    'code that works
    end sub

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If it's appropriate to your situation, you can force the second crosstab to include all the columns:

    http://allenbrowne.com/ser-67.html

    specifically the bit on column headings.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194
    Quote Originally Posted by Peter M View Post
    I'm a bit slow today and missing something in your code that tells me where it is looking for Column1, but can you configure it to loop like this:

    Code:
    For i = 1 to 10
      sub_routine(i)
    next i
    Where sub_routine(i) is the code that works, with i equal to the Column1 reference?

    e.g.
    sub_routine("1")
    sub_routine("2")

    Code:
    public sub sub_routine(var_ColumnHeading as integer)
    'code that works
    end sub
    Thank you so much for responding. I like this idea. I will try Monday to implement it, but I know I'll run into trouble. See, in my original post wks.Range("C1") is what points to column 1. When the value is found I wrote the code to move crosstab 2 information matching column 1 to column 1 w/ Destination:=wks.Range("C" & Lrow1 + 7). The trouble using your method is going to be how to write the code to match each column up because the destination won't be the same. I'll try to work on it though. Thanks again Peter!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. looping thru query
    By slimjen in forum Programming
    Replies: 5
    Last Post: 03-26-2015, 11:41 PM
  2. Looping thru fields.
    By Newby in forum Access
    Replies: 4
    Last Post: 01-29-2013, 03:42 PM
  3. Looping
    By ddrew in forum Forms
    Replies: 8
    Last Post: 10-08-2012, 01:48 AM
  4. Looping query
    By jaykappy in forum Queries
    Replies: 13
    Last Post: 02-24-2012, 03:05 PM
  5. Looping syntax
    By fadiaccess in forum Access
    Replies: 1
    Last Post: 10-23-2009, 02:57 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums