Application defined or Object defined error (for copying and pasting)

[kgbrown]

Sorry about the rudimentary nature of this question, but I am relatively new to VBA and can't work out what I am doing wrong. What I want to do is very simple: I have a sheet with four values on a row, followed by three blank rows and then another four values below that.

What I wish to do is basically fill the gaps: Copy the four values into the three empty gaps below it and then do the same for the next set of values. I can't do this manually because I have thousands of line of data. Just below is a rough example of the spreadsheet, with the x's representing blank cells.

1 2 3 4
x x x x
x x x x
x x x x
1 2 3 4
x x x x
x x x x
x x x x

My code is as follows...

Sub CopyDown()


Dim i, j As Integer

Application.ScreenUpdating = False

For i = 2 To 10

    Set curCell = Worksheets("Ppt 12").Cells(i, 3)
    
    j = i - 1
    
    If curCell.Value = "" Then
    
    Worksheets("Ppt 12").Range(i, 3).Value = Worksheets("Ppt 12").Range(j, 3).Value
        
    End If
    
Next i

Application.ScreenUpdating = True


End Sub

I keep getting the error "Application-definted or object defined error" 1004. I have tried quite a few different thing (like using absolute cell values instead of variables where possible and doing the same thing with select, copy and paste), but I still get the same error. I'm not sure where I am going wrong with such a simple thing...

Thanks in advance, I know it's probably a really simple problem, but it would massively help me out as it is just one stage in a series of things I need to do with this data.

 

[bensonsearch]

Hi,
The problem is something to do with the worksheet properties. sometimes I see this when a counter is set to 0 so it fails as there is no row 0. try setting i manually to start at 2 ie:

Dim i, j As Integer

Application.ScreenUpdating = False
 
i=2


For i = 2 To 10

    Set curCell = Worksheets("Ppt 12").Cells(i, 3)
    
    j = i - 1
    
    If curCell.Value = "" Then
    
    Worksheets("Ppt 12").Range(i, 3).Value = Worksheets("Ppt 12").Range(j, 3).Value
        
    End If
    
Next i

Application.ScreenUpdating = True

 

[kgbrown]

Many thanks for your reply.

Unfortunately I have just tried it and was getting the same error. I just managed to work it out however, it was because I was stating .range in the cell reference when I should have been stating .Cells.

Thanks again for your help though, really appreciate it.

 

[bensonsearch]

hahaha no worries, glad you worked it out