none
Moving text within either a for..next or do...until structure RRS feed

  • Question

  • Hello,

    I am having issues with moving text within a for...next or do...until structure, even though I do not know which structure is most appropriate.

    I want the program to perform the following:

    1. select the name

    2. cut 

    3. move left 

    4. move down

    5. paste

    6. move down 

    7. move right

    Repeat 

    Unless

    if indicator is detects bold then

    1. cut

    2. move left

    3. move down

    4. paste

    5. move right

    That is all I have expressed in my algorithm.

    Any answers for any user?

    Regards,

    JohnDBCTX

    Here is my screenshot:

    Excel VBA Screenshot


    jp

    Sunday, May 6, 2018 11:28 AM

Answers

  • It is rarely a good idea to select cells in a macro, it slows down execution.

    How about this:

    Sub MoveText()
        Dim r As Long
        Dim m As Long
        Application.ScreenUpdating = False
        m = Range("B" & Rows.Count).End(xlUp).Row
        For r = 2 To m Step 2
            Range("B" & r).Cut Destination:=Range("A" & r + 1)
        Next r
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by JohnDBCTX Sunday, May 6, 2018 1:05 PM
    Sunday, May 6, 2018 11:48 AM

All replies

  • Could you provide you code? Both [For -Next] and [Do Until].

    Ashidacchi -- http://hokusosha.com/

    Sunday, May 6, 2018 11:35 AM
  • It is rarely a good idea to select cells in a macro, it slows down execution.

    How about this:

    Sub MoveText()
        Dim r As Long
        Dim m As Long
        Application.ScreenUpdating = False
        m = Range("B" & Rows.Count).End(xlUp).Row
        For r = 2 To m Step 2
            Range("B" & r).Cut Destination:=Range("A" & r + 1)
        Next r
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by JohnDBCTX Sunday, May 6, 2018 1:05 PM
    Sunday, May 6, 2018 11:48 AM
  • Sub MoveText()
        Dim r As Long
        Dim m As Long
        Application.ScreenUpdating = False
        m = Range("B" & Rows.Count).End(xlUp).Row
        For r = 2 To m Step 2
            Range("B" & r).Cut Destination:=Range("A" & r - 1)
        Next r
        Application.ScreenUpdating = True
    End Sub
    

    Here is the improved screenshot below:

    Improved Solution Excel VBA


    jp

    The minus before the numerical value is to the left; whereas, the plus before the numerical value is to the right.

    Regards,

    JohnDBCTX

    • Edited by JohnDBCTX Sunday, May 6, 2018 1:18 PM clarification
    Sunday, May 6, 2018 1:17 PM
  • Is this a new question, or …?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, May 6, 2018 2:39 PM