Pasting inside a range, which is counting up the cells for its second parameter, the row

Question

I would like to paste data from a range, copy it in a new range. This new range is supposed to count up its row by +1 (represented by variable i) for each value (self) that is being pasted. I use self, because i need to extract values from a range that includes empty cells.

I tested the first part, the debug print works, so the self values are obtained correctly. I must be making a mistake with the Range(27, i) part, and also with the Set i = 15 part. Help very appreciated. sincerely

Private Sub EvaluateButton_Click()
    Dim EvaRange As Range   'Evaluation range
    Dim i As Integer        'Counter for free columns
      
    Worksheets("testsheet").Activate
    
    Set EvaRange = Range("C10:C999")
       EvaRange.Copy Range("Z15")
      
    Range("Z15:Z999").Select
        Set i = 15
        For Each self In Selection.SpecialCells(xlCellTypeConstants)
            Debug.Print (self)
            i = i + 1
            self.Copy Range(27, i)
    Next
                                                                
End Sub

Edit: i tried a second version, i think that variables are defined like this: Dim x As Integer x = 6

So shouldn't this work?

    Private Sub EvaluateButton_Click()
    Dim EvaRange As Range  
    Dim i As Integer     
      
    Worksheets("testsheet").Activate
    
    Set EvaRange = Range("C10:C999")
       EvaRange.Copy Range("Z15")
      
    Range("Z15:Z999").Select
        i = 15
        For Each self In Selection.SpecialCells(xlCellTypeConstants)
            Debug.Print (self)
            self.Copy Range(27, i)
            i = i + 1
    Next

Please show us sample data and a some data that represent the result. Otherwise it is pretty hard to understand what you want to achiev (reading [ask] and [repro] should help you to enhance your question.) Furthermore: you don't need select/copy/paste -->read [How to avoid select](https://stackoverflow.com/q/10714251/16578424) — Ike, Apr 28 '22 at 09:40
`Set i = 15` should be `i = 15`. You cannot say `Range(x, y)`; you'll want `Cells(x, y)`. So, write: `Cells(27, 15 + i) = self`. Try, indeed, to avoid select/copy/paste — ouroboros1, Apr 28 '22 at 09:46

score 0 · Answer 1 · answered Apr 28 '22 at 09:46

This will remove my empty lines :) Now, I have the problem, that my last value is duplicated down the line, a new challenge to handle :)

Private Sub EvaluateButton_Click()
    Dim EvaRange As Range   'Evaluation range
    Dim i As Integer        'Counter for free columns
      
    Worksheets("testsheet").Activate
    
    Set EvaRange = Range("C10:C999")
       EvaRange.Copy Range("Z15")
      
    Range("Z15:Z999").Select
        i = 15
        For Each self In Selection.SpecialCells(xlCellTypeConstants)
            Debug.Print (self)
            self.Copy Cells(i, 27)
            i = i + 1
    Next

Pasting inside a range, which is counting up the cells for its second parameter, the row

1 Answers1