I'm having trouble pasting data into specific cells in a table

**phanjoe** · 09-15-2012, 07:43 PM

I have been trying to work out a way to post specific dates against names and dates within a table. I have found the attached way with if,then, else, statements. but with a large table there are too many lines of code to function properly. Does anyone know of an easier method it would be much appreciated.

**bheanloh** · 09-15-2012, 08:03 PM

I don't really know what you want, but u can grab something out of this code. It will copy and paste the date to Date1 if D5 to the D(last row) is equal to 1.

Sub test()
Dim Rng as Range
With Sheets("Sheet1")
For Each Rng In .Range("D5:D" & .Cells(Rows.Count, 4).End(xlUp).Row)
    If Rng.Value = 1 Then .Range("S10").Copy Rng.Offset(, 2)
Next
End With
End Sub

Boon

**phanjoe** · 09-15-2012, 09:12 PM

thanks for that i'll have a try and use what i can

**phanjoe** · 09-19-2012, 09:43 PM

I have had a look at the previous suggestion, which was really good and much better than I could do.
However the problem I have is that I need to vary the columns date 1 to date 5 which is dependent on the selection from the drop down box to populate into the relevant row with the ticked box.
Any ideas would be appreciated.Book1.xlsm

**bheanloh** · 09-21-2012, 11:36 PM

Is this what you want? I'm just guessing.

Sub test()
Dim i As Integer, j As Integer, a As Range
Dim Rng As Range, rng2 As Range
i = 0: j = 0
With Sheets("Sheet1")
For Each Rng In .Range("D5:D" & .Cells(Rows.Count, 4).End(xlUp).Row)
    j = j + 1
    If Rng.Value = 1 Then
        For Each rng2 In Rng.Offset(, 1).Resize(1, 5)
            i = i + 1
            If rng2 = 1 Then
                Set a = .Cells.Find(what:="Date " & i, lookat:=xlWhole, MatchCase:=False)
                i = 0: Exit For
            End If
        Next
        .Cells(10, 19).Copy a.Offset(j)
    End If
Next
End With

End Sub

By the way, you need to fill E6 to I9 with the same formula you have in E5 to I5