Results 1 to 5 of 5

Thread: VBA Cutting/Pasting from one column to another depending on variable

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Cutting/Pasting from one column to another depending on variable

    Good Evening, I'm still fairly new to VBA and my googling hasn't gotten me results. I'm trying to move data from 3 columns to a different 3 columns, same workbook and sheet, even the same row. The column data is being moved to depends on what is in the first column, from what I can tell this requires a Case statement. I've entered 3 of the values in my code so far and when I run the macro nothing actually happens. See code below.

    Code:
    Sub MoveTax()
    
    
    Select Case Column1
    Case "CA1"
    Range("A:C").Cut Range("D:F")
    
    
    Case "PA12"
    Range("A:C").Cut Range("G:I")
    
    
    Case "PA10"
    Range("A:C").Cut Range("G:I")
    End Select
    
    
    End Sub


    This is the desired outcome:

    Column 1 Column 2 Column 3 Column 4 Column 5 Column 6 Column 7 Column 8 Column 9
    CA1 $3 $8 here here here
    CA1 $5 $7 here here here
    PA10 $8 $9 here here here
    PA12 $13 $19 here here here










    This is where I was getting my information from and I'm sure it's lacking context that I'm not able to determine: https://www.techonthenet.com/excel/formulas/case.php

  2. #2
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,572
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA Cutting/Pasting from one column to another depending on variable

    Code:
    Dim rng As Range, cel As Range
    Set rng = Range("A2:A" & Cells(Rows.Count, "A").End(3).Row)
    For Each cel In rng
        Select Case cel
            Case "CA1": cel.Resize(, 3).Cut cel(1, 4)
            Case "PA10", "PA12": cel.Resize(, 3).Cut cel(1, 7)
        End Select
    Next

  3. #3
    New Member
    Join Date
    Jul 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Cutting/Pasting from one column to another depending on variable

    Quote Originally Posted by footoo View Post
    Code:
    Dim rng As Range, cel As Range
    Set rng = Range("A2:A" & Cells(Rows.Count, "A").End(3).Row)
    For Each cel In rng
        Select Case cel
            Case "CA1": cel.Resize(, 3).Cut cel(1, 4)
            Case "PA10", "PA12": cel.Resize(, 3).Cut cel(1, 7)
        End Select
    Next
    You're awesome, this does what I need. Can you by chance explain to me what is going on with each Case statement and why they are written different? Reason I ask is I have up to 61 of these codes that will need shuffled in to columns depending on which code it is, so PA10 and 12 would go in those columns but PA15 would go in a different set of 3. I was hoping to replicate this script with the others and essentially update the "CA/PA/1/10/12" part of the string.

    From what I'm understanding in the CA1 statement it's cutting 3 cells beginning with column 1 and pasting them in to 3 cells beginning with column 4? If my understanding of this is correct then I shouldn't have any problem duplicating your code. Again, thank you for your help!

  4. #4
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,572
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA Cutting/Pasting from one column to another depending on variable

    Your understanding of the code is correct.
    The Case statements could also be written like this :
    Code:
    Case "CA1": cel.Resize(, 3).Cut cel.Offset(0, 3)
    Case "PA10", "PA12": cel.Resize(, 3).Cut cel.Offset(0, 6)
    It is not necessary to put PA10 and PA12 on separate lines since the action based these values is the same.

  5. #5
    New Member
    Join Date
    Jul 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Cutting/Pasting from one column to another depending on variable

    The .Offset code actually worked better for my needs. You saved my bacon. Thank you!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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