Results 1 to 6 of 6

Thread: [RESOLVED] Looping to next column

  1. #1

    Thread Starter
    New Member
    Join Date
    Apr 2006
    Posts
    3

    Resolved [RESOLVED] Looping to next column

    I am looking to use a loop to populate the next column and am difficulties. The only way I am able to get it to work is to just past a new column each time, but that is making the order of the looping go backwards. Is there a way to move through columns and not have to insert a column? Any help is greatly appreciated. TIA
    Here is the code:

    VB Code:
    1. 'lets loop
    2. While dTempStart < dEnd
    3.  
    4.     'create a new column for the data
    5.     Sheets("Setup").Select
    6.     Columns("F:F").Select
    7.     Selection.Insert Shift:=xlToRight
    8.    
    9.     'plug the dates into the worksheet
    10.     Worksheets("Setup").Range("C3").Value = dTempStart
    11.     Worksheets("Setup").Range("C4").Value = dTempEnd
    12.    
    13.     'put worksheet output into the new column
    14.     Sheets("Setup").Select
    15.     Columns("C:C").Select
    16.     Selection.Copy
    17.     Columns("F:F").Select
    18.     Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    19.         xlNone, SkipBlanks:=False, Transpose:=False
    20.     Columns("C:C").Select
    21.     Application.CutCopyMode = False
    22.      Range("E11").Select
    23.        
    24.     'setup for next iteration of the loop
    25.     dTempStart = dTempEnd + 1
    26.     dTempEnd = dTempEnd + interval - 1
    27. Wend
    Last edited by RobDog888; Apr 18th, 2006 at 11:04 PM. Reason: Added [vbcode] tags

  2. #2
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,713

    Re: Looping to next column

    Looks like your trying to past a column of data but why are you inserting a new one? Why not just change the destination column of the paste?
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  3. #3

    Thread Starter
    New Member
    Join Date
    Apr 2006
    Posts
    3

    Re: Looping to next column

    Instead of inserting a new column and using that column continually, I would like to paste into the next column, but am unsure of how to get the loop to go to the next column.

    When moving to the next row, you can just create something like this, but as far as moving to the next column, I am a little lost.

    Worksheets("Setup").Range("B13").Value = "N"
    i = 4
    While Worksheets("PrintList").Range("D" + CStr(i)).Value > ""
    Worksheets("Test").Range("B2").Value = Worksheets("PrintList").Range("D" + CStr(i)).Value
    Worksheets("Printlist").Range("E" + CStr(i)).Value = Worksheets("Test").Range("G40").Value
    Worksheets("Printlist").Range("F" + CStr(i)).Value = Worksheets("Test").Range("G42").Value
    Worksheets("Printlist").Range("G" + CStr(i)).Value = Worksheets("Test").Range("K40").Value
    Worksheets("Printlist").Range("H" + CStr(i)).Value = Worksheets("Test").Range("K42").Value
    Worksheets("Printlist").Range("R23").Value = i
    i = i + 1

  4. #4
    Frenzied Member DKenny's Avatar
    Join Date
    Sep 2005
    Location
    on the good ship oblivion..
    Posts
    1,171

    Re: Looping to next column

    The following version of your original code will copy to the next free column of the "Setup" worksheet. It assumes that you can determine which column is the starting "copy to" column. (I used colum F)
    I've added a Long variable "lPasteColumn" to store the column number, you will need to add this to you variables declaration section.
    All that happens is that as you loop you increment the column number by one to move to the next column.
    Also, I have replaced your WHILE...WEND with a DO..LOOP which is a better loopinf structure.

    VB Code:
    1. 'Assume Column F is the first column
    2. 'to copy to
    3. lPasteColumn = 6
    4.  
    5. 'lets loop
    6. Do While dTempStart < dEnd
    7.    
    8.     With Sheets("Setup")
    9.         'Change the Start and End Dates
    10.         .Range("C3").Value = dTempStart
    11.         .Range("C4").Value = dTempEnd
    12.        
    13.         'Copy Column C to the Destination column
    14.         .Columns(4).Copy
    15.         .Columns(lPasteColumn).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    16.         xlNone, SkipBlanks:=False, Transpose:=False
    17.     End With
    18.    
    19.     'Clear the Clipboard
    20.     Application.CutCopyMode = False
    21.        
    22.        
    23.     'setup for next iteration of the loop
    24.     dTempStart = dTempEnd + 1
    25.     dTempEnd = dTempEnd + interval - 1
    26.     lPasteColumn = lPasteColumn + 1
    27. Loop
    Declan

    Don't forget to mark your Thread as resolved.
    Take a moment to rate posts that you think are helpful

  5. #5

    Thread Starter
    New Member
    Join Date
    Apr 2006
    Posts
    3

    Re: Looping to next column

    DKenny~

    Thank you, that worked. I was definately struggling with the move to next column.

    Summer0897

  6. #6
    Frenzied Member DKenny's Avatar
    Join Date
    Sep 2005
    Location
    on the good ship oblivion..
    Posts
    1,171

    Re: Looping to next column

    OK, glad I could help. Don't forget to mark this thread as resolved if your question has been answered. its under "Thread Tools" at the top of the page.
    Declan

    Don't forget to mark your Thread as resolved.
    Take a moment to rate posts that you think are helpful

Posting Permissions

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



Click Here to Expand Forum to Full Width