Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Oct 2014
    Posts
    13
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Looking for VBA code to perform find, copy, insert, replace, and split

    I'm extremely new to VBA and I have an issue that cannot be done with the macro recorder. I'm looking for a macro to search for all instances of "JM" within column E, copy that entire row and insert into a row below. Then I need it to replace the first (original) "JM" with "J" and the new (copied) "JM" with "M". I also need the values in the amount column to be divided by 2 in both rows. For example:

    CURRENT DATA:
    A B C D E
    SEQ12647 100.00 DESC12647 7/1/2014 JM


    NEEDS TO BE:
    A B C D E
    SEQ12647 50.00 DESC12647 7/1/2014 J
    SEQ12647 50.00 DESC12647 7/1/2014 M


    Not entirely sure if this is possible with a macro, but any help would be much appreciated. Please let me know if this is unclear. Thanks

  2. #2
    New Lounger
    Join Date
    Oct 2014
    Posts
    13
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I have a macro in place to copy and insert the row (below). If someone could just help with the other two functions (splitting JM and dividing amount by 2), that would be great.


    Sub JMTEST ()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim Lst As Long
    Dim n As Long
    Lst = Range("E" & Rows.Count).End(xlUp).Row
    For n = Lst To 2 Step -1
    With Range ("E" & n)
    If .Value = "JM" Then
    .EntireRow.Copy
    .Offset(1).EntireRow.Resize.Insert
    End If
    End With
    Next n
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    End Sub

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    KMF,

    Welcome to the Lounge as a new Poster!

    You were so close!

    Code:
    Option Explicit
    
    Sub JMTEST()
    
       Dim Lst As Long
       Dim n As Long
    
       Application.ScreenUpdating = False
       Application.Calculation = xlCalculationManual
       
       Lst = Range("E" & Rows.Count).End(xlUp).Row
       
       For n = Lst To 2 Step -1
          With Range("E" & n)
              If .Value = "JM" Then
                .EntireRow.Copy
                .Offset(1).EntireRow.Resize.Insert
                Application.CutCopyMode = False
                .Value = "J"
                .Offset(1).Value = "M"
              End If
          End With
       Next n
       
       Application.ScreenUpdating = True
       Application.Calculation = xlCalculationAutomatic
       
    End Sub
    Starting Test Data: (Note: Column F added for reference)
    JMOrigData.JPG

    Data After Running Macro:
    JMAfterRun.JPG

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. The Following User Says Thank You to RetiredGeek For This Useful Post:

    kmf (2014-10-08)

  5. #4
    New Lounger
    Join Date
    Oct 2014
    Posts
    13
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thank you! Only problem left is how to get the amount column to divide by two (half in original row and half in the copied row). Is it possible to do this within the same macro?

  6. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    KMF,

    Code:
    Option Explicit
    
    Sub JMTEST()
    
       Dim Lst     As Long
       Dim n       As Long
       Dim dAmount As Double
    
       Application.ScreenUpdating = False
       Application.Calculation = xlCalculationManual
       
       Lst = Range("E" & Rows.Count).End(xlUp).Row
       
       For n = Lst To 2 Step -1
          With Range("E" & n)
              If .Value = "JM" Then
                .EntireRow.Copy
                .Offset(1).EntireRow.Resize.Insert
                Application.CutCopyMode = False
                dAmount = Round(.Offset(0, -3).Value / 2, 2)
                .Value = "J"
                .Offset(1).Value = "M"
                .Offset(0, -3).Value = dAmount
                .Offset(1, -3).Value = dAmount
              End If
          End With
       Next n
       
       Application.ScreenUpdating = True
       Application.Calculation = xlCalculationAutomatic
       
    End Sub
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #6
    New Lounger
    Join Date
    Oct 2014
    Posts
    13
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thank you!!

  8. #7
    New Lounger
    Join Date
    Oct 2014
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you! Only problem left is how to get the amount column to divide by two (half in original row http://www.vcefiles.net/notes/vce-mobile.php and half in the copied row). Is it possible to do this within the same macro?

  9. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Edward,

    The code in post #5 does that.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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