Need Loop code to Copy data or pass values

**CodeNublet** · 02-17-2012, 07:33 AM

Hi I have inherited some files that will need to be updated monthly. The data is sales information. The problem is it has gaps in columns and rows that need populating. I have tried using a DO/Loop statement with ActiveCell to do this but can't get the logic right. I have attached a spreadsheet with a dummy example of before and after. Accomplishing this with code will save me hours of work each month.
As you can see my code is flawed but hopefully you can see what I am trying to accomplish.
If anyone can help I would be really grateful.

PHP Code:


Sub Copydata()

Dim MyCell As String

Dim i As Integer

'i = 2314

'Do Until IsEmpty(Cells(i, 2))

For i = 1 To 2314 Step 1

Range("c3").Select

MyCell = ActiveCell.Value



If ActiveCell.Offset(1, 0).Value = "" Then

ActiveCell.Offset(1, 0).Select

ActiveCell.Value = MyCell

    

Else: ActiveCell.Offset(1, 0).Select

MyCell = ActiveCell.Value



End If

ActiveCell.Offset(1, 0).Select

i = i + 1



Next i

ActiveCell.Offset(1, 0).Select

End Sub[

**JapanDave** · 02-17-2012, 08:02 AM

Hey ,

see if this is what you are after?

Sub Copydata()
   Dim c01, c02, c03, c04
   Dim rng01 As Range
    Application.ScreenUpdating = 0
     Set rng01 = Cells(5, 1).CurrentRegion
       
         For Each c01 In rng01
            If c01 = "" Then
               c01.Value = c01.Offset(-1).Value
            End If
         
         Next
    Application.ScreenUpdating = 1
End Sub