Results 1 to 3 of 3
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,568
    Thanks
    141
    Thanked 12 Times in 12 Posts

    Macro to copy data , except where = sum appears

    I have code to copy the Data in Col F (closing Balances) and to paste value these in Col C (Opening Balances) from row 3 onwards, except where the data contains an = sum formula. Where there is an =sum formula In Col F and Col C this must be left intact.

    I have written code to copy the data from Col F to C , except not to copy the = sum( formula, but after running the macro, the opening bal is incorrect. for eg Corporate signage closing balance before running the macro was 11,477.00 -after running the macro the opening bal for corporate signage is 20,311.00 but should be 11,477



    Code:
     Sub Copy_Closing_Balances()
    Sheets("sheet1").Select
    Dim lr As Integer, Lr1 As Integer
    
    lr = Range("F3").End(xlDown).Row
    
        For I = 3 To lr
            If Left(Range("F" & I).Formula, 4) = "=SUM" Then
                    Else
                Range("C" & I).Value = Range("f" & I).Value
            End If
        Next I
    Lr1 = Range("F16").End(xlDown).Row
    
        For J = 3 To Lr1
            If Left(Range("F" & J).Formula, 4) = "=SUM" Then
                    Else
                Range("C" & J).Value = Range("f" & J).Value
            End If
        Next J
    End Sub

    Attached please find my workbook before running the macro as well as after running the macro. It would be appreciated if someone could assist in resolving the problem

    I have also posted on Mr Excel.com

    http://www.mrexcel.com/forum/excel-q...copy-data.html
    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    3,038
    Thanks
    166
    Thanked 800 Times in 729 Posts
    Howard,

    When you get to the J loop, you are starting back at row 3 and duplicating the first loop therefore doubling your numbers.

    Change
    For J = 3 To Lr1
    to
    For J = 16 To Lr1

    That should solve your problem

    HTH,
    Maud

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,568
    Thanks
    141
    Thanked 12 Times in 12 Posts
    Thanks Maud, never thought of this. Have amended code and it works perfectly

Posting Permissions

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