Results 1 to 3 of 3
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 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
    2,631
    Thanks
    115
    Thanked 645 Times in 589 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,420
    Thanks
    124
    Thanked 5 Times in 5 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
  •