Problem copying and pasting in excel macro

Question

When "Add 2 points" is detected in "N4:N203", I need to add 2 to corresponding cell in "E4:K4". Then copy "AB4:AB203" to "O4:O203" to "O4:O203".

Sub Moving_tees_add_2()
Dim PointsToAdd As Integer

    PointsToAdd = 2

        Sheets("MEMBERS1").Select
Application.ScreenUpdating = False
            Range("C4").Select

    Do Until ActiveCell.Row = 204

        If ActiveCell.Range("L1").Value = ("Add 2 points") Then
        If ActiveCell.Range("C1").Value <> "n/a" Then ActiveCell.Range("C1").Value = ActiveCell.Range("C1").Value + PointsToAdd
        If ActiveCell.Range("D1").Value <> "n/a" Then ActiveCell.Range("D1").Value = ActiveCell.Range("D1").Value + PointsToAdd
        If ActiveCell.Range("E1").Value <> "n/a" Then ActiveCell.Range("E1").Value = ActiveCell.Range("E1").Value + PointsToAdd
        If ActiveCell.Range("F1").Value <> "n/a" Then ActiveCell.Range("F1").Value = ActiveCell.Range("F1").Value + PointsToAdd
        If ActiveCell.Range("G1").Value <> "n/a" Then ActiveCell.Range("G1").Value = 
    ActiveCell.Range("G1").Value + PointsToAdd
        If ActiveCell.Range("H1").Value <> "n/a" Then ActiveCell.Range("H1").Value =  
    ActiveCell.Range("H1").Value + PointsToAdd
        If ActiveCell.Range("I1").Value <> "n/a" Then ActiveCell.Range("I1").Value = 
    ActiveCell.Range("I1").Value + PointsToAdd
        
        If ActiveCell.Range("C1").Value <> "n/a" Then ActiveCell.Range("Q1").Value = 
    ActiveCell.Range("Q1").Value + PointsToAdd
        If ActiveCell.Range("D1").Value <> "n/a" Then ActiveCell.Range("R1").Value = 
    ActiveCell.Range("R1").Value + PointsToAdd

    Range("AB4:AB203").Select
    Selection.Copy
    Range("O4:O203").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    'ActiveCell.Range("A1").Select
    '
    '        Selection.ClearContents
    Range("N4:N203").Select
        Selection.ClearContents
    End If

        ActiveCell.Offset(1, 0).Range("A1").Select
    
    Loop

So what's the specific problem you're having with the code you posted? — Tim Williams, Jun 16 '22 at 15:22
I would first start by updating your code to not use Select/Activate: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — Tim Williams, Jun 16 '22 at 15:25
It works on the first row it finds the text on, but no subsequent rows — Charles Langston, Jun 16 '22 at 15:27
Because you have `Range("N4:N203").Select` right before `ActiveCell.Offset(1, 0).Range("A1").Select` — Tim Williams, Jun 16 '22 at 15:29
First fix would be to not use select/activate: you get a lot of that from the macro recorder but you should try not to use it, since it's very rarely necessary to select a range to work with it. See the link I posted above, and my answer below. — Tim Williams, Jun 16 '22 at 15:37

Tim Williams · Answer 1 · 2022-06-16T15:51:17.870

0

Without all the select/activate:

Sub Moving_tees_add_2()
    Dim PointsToAdd As Long, rw As Long, ws As Worksheet, c As Range

    Set ws = ThisWorkbook.Sheets("MEMBERS1") 'use a worksheet reference
    PointsToAdd = 2

    Application.ScreenUpdating = False
            
    For rw = 4 To 204
        With ws.Rows(rw)
            If .Columns("N").Value = "Add 2 points" Then
                'loop over the range to be incremented
                For Each c In .Range("E1:K1,S1:T1").Cells
                    If c.Value <> "n/a" Then c.Value = c.Value + PointsToAdd
                Next c
                'you can directly assign the value from one range to another,
                '  without copy/pastespecial
                ws.Range("O4:O203").Value = ws.Range("AB4:AB203").Value
            End If
        End With
    Next rw

End Sub

edited Jun 16 '22 at 15:51

answered Jun 16 '22 at 15:36

Tim Williams

154,628
8
97
125

This almost works but adds a different number each time run.. need it to add 2 each time – Charles Langston Jun 16 '22 at 15:42
If `PointsToAdd` is always 2 I'm not sure how that code could add a different number from that? – Tim Williams Jun 16 '22 at 15:44
sometimes adds 1, 2, 3, etc – Charles Langston Jun 16 '22 at 15:46
You had an extra 2 lines incrementing cells in "Q1" and "R1" - I left those out since they were'nt in your description where only columns E:K were incremented – Tim Williams Jun 16 '22 at 15:47
Sorry but I can't imagine adding any number than 2. Do you have some sample data you can share? – Tim Williams Jun 16 '22 at 15:49
the 2 extras should be S and T – Charles Langston Jun 16 '22 at 15:49
OK - added that S:T above – Tim Williams Jun 16 '22 at 15:51
i see why. Columns S and T are a part of the equation that calculates col O. Without them the formula is off. How do I also add 2 to columns S and – Charles Langston Jun 16 '22 at 15:55
i see why. Columns S and T are a part of the equation that calculates col O. Without them the formula is off. How do I also add 2 to columns S and T – Charles Langston Jun 16 '22 at 15:55
`For Each c In .Range("E1:K1,S1:T1").Cells` - see edit above – Tim Williams Jun 16 '22 at 15:56
You nailed it! I can't thank you enough for the code and the lesson. – Charles Langston Jun 16 '22 at 15:58
You're welcome - good to hear it's working – Tim Williams Jun 16 '22 at 16:00

Problem copying and pasting in excel macro

1 Answers1