Consulting

Results 1 to 18 of 18

Thread: Solved: Rearranging columns

  1. #1
    VBAX Regular
    Joined
    Apr 2010
    Posts
    17
    Location

    Solved: Rearranging columns

    Hello, I have spent way too much time on this and could use some help. I am simply attemtping to rearrange coulns of data using the code:

    Sub Rearrange()
    Columns("D").Select
    Selection.Cut
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight

    End Sub

    When I step through the code it works perfectly fine. However, when I run the entire program it does not work properly. Instead of inserting Column D before Column A, Couluns A are simply shifted to the right one column. Its as if the Cut method is not recognized.

    Thoughts?

  2. #2
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,002
    Location
    This works fine for me
    [VBA]Sub Rearrange()
    Columns("D").Select
    Selection.Cut
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight
    End Sub
    [/VBA]
    Peace of mind is found in some of the strangest places.

  3. #3
    VBAX Regular
    Joined
    Apr 2010
    Posts
    17
    Location
    I fave figured out that it has something to do Application.Screenupdating =false. If I remove this line from my program, the code works. Any suggestions?

  4. #4
    VBAX Contributor
    Joined
    Dec 2009
    Location
    Sevastopol
    Posts
    150
    Location
    It works for me with Application.ScreenUpdating as well.
    May be worksheet event SelectionChange triggers in your code, you can switch Application.EnableEvents = False/True
    BTW - selecting is not required, try:
    
    Sub Rearrange()
      With Application
        .ScreenUpdating = False
        .EnableEvents = False
        Columns("D").Cut
        Columns("A").Insert Shift:=xlToRight
        .EnableEvents = True
        .ScreenUpdating = True
      End With
    End Sub

  5. #5
    VBAX Regular
    Joined
    Apr 2010
    Posts
    17
    Location
    Thank you for your attempts to help, but EnableEvents is not the solution.

    Again, here is my issue:
    I have a simple piece of code:

    Columns("C:C").Cut ''''' GET THIS TO WORK !!!!!!!!
    Columns("A:A").Insert Shift:=xlToRight

  6. #6
    VBAX Regular
    Joined
    Apr 2010
    Posts
    17
    Location
    Sorry, I accidentally sent the above reply without finishing.

    The simple piece of code from above is embedded in a much larger program. Why might the code work when Screenupdating is on but it does not when Screenupdating = false????

  7. #7
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,002
    Location
    Well since your new here let me explain something to you. If you want help, demanding will get you noting but ignored. We are not paid to help you. People come here and help because they want to not because they have to.

    Several people have tested what you posted and it works. However there seems to be a bigger issue. Try posting the whole macro or just post a sample workbook. No one that would help you can diagnose the problem with such little information. In the future be polite. Not demanding. Shouting (all caps and !!!!!) is the best way to get yourself ignored.

    Ok I'm off my soap box now.
    Peace of mind is found in some of the strangest places.

  8. #8
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    This works for me
    [VBA]
    Columns(4).Cut
    Columns(1).Insert

    [/VBA]
    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
    VBAX Regular
    Joined
    Apr 2010
    Posts
    17
    Location
    Austenr,
    I am very sorry that my previous message came off as rude or impolite. There seems to be a slight miscommunication. The all caps "GET THIS TO WORK" was not directed towards you or this website - I included the comment in my code as a reminder to myself of where the problem is. I can see how this looks bad as part of my post and I apologize. And you are right, I am new here and I have learned my lesson. I am simply looking for a bit of help and it certainly was not my intention to offend anybody.

    as far as providing more information, I was hoping that there was an obvious solution that somebody with more experience than I would see right away. I was hoping to avoid posting the entire macros. But here they are ...

    The program simply opens a word document, copies it into excel, and then formats the excel sheet in the sub GetUSAfile. GetUSAfile is called from Main. As you can see, i turn off Scree updating at the beginning of main. The problem code is located at near the end of GetUSA file. Again, it works fine when stepping through using the debugger and if I leave screenupdating on. I am stuck ... any help would be greatly appreciated.
    [vba]
    Option Explicit
    Sub Main()
    Application.ScreenUpdating = False
    Dim wrdApp As Word.Application
    Dim wrdDoc As Word.Document
    Dim name As String

    Call GetFile(name)
    Set wrdApp = CreateObject("Word.Application")
    ' Set wrdDoc = wrdApp.Documents.Open("C:\Program Files\Prime Analytics\ProOpticus\pldata\04_02_10.keswheat", , False)
    ' hardcoded directory

    Set wrdDoc = wrdApp.Documents.Open(name)
    wrdApp.ActiveWindow.Selection.WholeStory
    wrdApp.ActiveWindow.Selection.Copy
    Sheet1.Activate
    ActiveSheet.Range("a1").Select
    ActiveSheet.Paste
    wrdApp.Application.Quit


    PasteFile 'calls PasteFile()

    ' MsgBox "File Date: " & name
    RetrieveUSAfile 'calls RetrieveUSAfile()

    ActiveSheet.Range("a1").Select

    Application.ScreenUpdating = True
    End Sub


    Sub RetrieveUSAfile()


    Application.Workbooks.Open ("C:\Documents and Settings\Adam Keslosky\Desktop\TRADING\USAfile\b3145-pos")

    Dim i As Integer, lastline As Integer

    Workbooks("b3145-pos").Activate
    ActiveSheet.Range("a:b, e:f, j:k, m:o").Delete
    Columns("A:A").ColumnWidth = 10
    ActiveSheet.Range("1:1").Font.Bold = True
    lastline = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 2 To lastline 'fixes sign on position
    If Cells(i, 3) = 2 Then Cells(i, 10) = Cells(i, 4) * -1 _
    Else: Cells(i, 10) = Cells(i, 4)
    Next i
    Range(Cells(2, 10), Cells(lastline, 10)).Cut Range("d2")
    ActiveSheet.Range("c:c").Delete

    For i = lastline To 2 Step -1 'deletes all non-wheat rows
    If Cells(i, 5) <> "W-" Then Rows(i).Delete
    Next i
    ActiveSheet.Range("e:e").Delete

    lastline = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 2 To lastline ' fills C/P/F and Srike Columns appropriately
    If Cells(i, 2) = 0 Then Cells(i, 1) = "F"
    Cells(i, 2) = Cells(i, 2) * 100
    Next i
    Range("a:g").Copy
    Workbooks("ProOptFile").Worksheets("Sheet2").Activate
    Range("a1").Select
    ActiveSheet.Paste
    ActiveSheet.Cells(1, 1) = "C/P/F"
    ActiveSheet.Cells(1, 2) = "Strike"
    ActiveSheet.Cells(1, 3) = "Position"
    ActiveSheet.Cells(1, 4) = "Month"
    ActiveSheet.Cells(1, 5) = "Settle"
    ActiveSheet.Cells(1, 6) = "t-1 Settle"
    ActiveSheet.Range("e1").Select

    Dim code As String

    For i = 1 To lastline
    code = Cells(i, 4)
    If Left(code, 3) = "CAL" Then Cells(i, 4) = Right(code, Len(code) - 5)
    If Left(code, 3) = "PUT" Then Cells(i, 4) = Right(code, Len(code) - 5)
    Next i


    Columns("e:i").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

    Columns("d:d").Select

    Selection.TextToColumns DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True

    For i = 2 To lastline
    Select Case Cells(i, 4)
    Case "JAN"
    Cells(i, 4) = "F"
    Case "FEB"
    Cells(i, 4) = "G"
    Case "MAR"
    Cells(i, 4) = "H"
    Case "APR"
    Cells(i, 4) = "J"
    Case "MAY"
    Cells(i, 4) = "K"
    Case "JUN"
    Cells(i, 4) = "M"
    Case "JUL"
    Cells(i, 4) = "N"
    Case "AUG"
    Cells(i, 4) = "Q"
    Case "SEP"
    Cells(i, 4) = "U"
    Case "OCT"
    Cells(i, 4) = "V"
    Case "NOV"
    Cells(i, 4) = "X"
    Case "DEC"
    Cells(i, 4) = "Z"
    Case Else
    Cells(i, 4).Value = "???"
    End Select
    Next i
    Range("f:h").Delete
    Range("e1") = "Year"
    Columns("g:g").Interior.ColorIndex = 16
    Columns("h:h").Interior.ColorIndex = 15

    ' Columns("C:C").Cut ''''' PROBLEM
    ' Columns("A:A").Insert Shift:=xlToRight

    Workbooks("b3145-pos").Activate ' closes b3145-pos file
    Application.CutCopyMode = False
    Range("a1").Select
    Application.DisplayAlerts = False
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
    Workbooks("ProOptFile").Worksheets("Sheet1").Activate

    End Sub
    [/vba]

  10. #10
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,002
    Location
    No problem. Didn't mean to come off so harsh. Anyway, seems like a lot of this code could be simplified. It would help if you could post the word doc(s) and excel workbook in the code minus any sensitive data in order for testing to be done unless someone sees something obvious.
    Peace of mind is found in some of the strangest places.

  11. #11
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Try updating before the cut paste. You could even try forcing a recalculation and allowing any unrelated events to be carried out. With testing these extras could be deleted if not required


    [VBA]
    Application.ScreenUpdating = True
    DoEvents
    Calculate
    Columns("C:C").Cut ''''' PROBLEM
    Columns("A:A").Insert
    Application.ScreenUpdating = False
    [/VBA]
    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
    VBAX Regular
    Joined
    Apr 2010
    Posts
    17
    Location
    How do I post entire workbooks and word docs?

  13. #13
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Use Manage Attachments in the Go Advanced reply section
    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'

  14. #14
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,002
    Location
    Go to the Go Advanced button then Manage Attachments.
    Peace of mind is found in some of the strangest places.

  15. #15
    VBAX Regular
    Joined
    Apr 2010
    Posts
    17
    Location
    Okay ...
    So I have attached:
    1. My workbook entitled "ProOptFile" including all code
    2. a word file entitled "04_16_10" (Please note the file name is date-specific"


    This is how the program works:
    From Main, the program opens the Word doc and pastes it into the Excel workbook. Then, Sub Pastefile() is called to perform various formatting instructions. The formatting works fine until reaching the end of Pastefile() when I attempt to rearrange the columns using Cut/Insert methods

    The Problem:
    Ideally, I would like to turn Screenupdating off at the beginning of Main()to optimize performance. However, the arranging of columns does not work properly with Screenupdating off. If I delete Screenupdating = false at the beginning of Main(), the formatting works fine.

    Any help would be greatly appreciated!

  16. #16
    VBAX Regular
    Joined
    Apr 2010
    Posts
    17
    Location
    word file attached.

    Note: instructions to open file may have to be changed within code

  17. #17
    VBAX Contributor
    Joined
    Dec 2009
    Location
    Sevastopol
    Posts
    150
    Location
    It doesn’t matter if ScreanUpdating is True or False.
    But does if some interactive message suspends the code. For example at debugging, or in case of sheet "ProOpt" was not cleared before running of the Main macro and the warning message to replace the data occurs.

    But the reason of issue is in absence of releasing the memory for automation Word object variables wrdDoc and wrdApp by setting it to Nothing.

    To solve the problem add two lines (in red) into the Main subroutine as follows:
    
    Sub Main()
    
      Application.ScreenUpdating = False
    
      ' ... Skipped part of the code ...
    
      wrdApp.Application.Quit
    
      Set wrdDoc = Nothing  ' <-- Release the memory of the wrdDoc object variable
      Set wrdApp = Nothing  ' <-- Release the memory of the wrdApp object variable
    
      'Range("a:a").Select  ' <-- It's not required
    
      PasteFile
    
      Application.ScreenUpdating = True
    
    End Sub
    Regards,
    Vladimir

  18. #18
    VBAX Regular
    Joined
    Apr 2010
    Posts
    17
    Location
    That works! Thank you Vladimir! And thanks to all others that helped.

Posting Permissions

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