Paste to row problem

[sharky12345]

Can't seem to work this out - I'm trying to copy userform values to a sheet but with the following criteria;

1) Each cell from C4 down is NOT empty
2) For each of those cells, the adjacent cell in column D IS empty

I've played around with this but it just keeps overwriting my existing data in column C.

Dim lr As Long, c As Range

lr = Sheets("Packs").Cells(Rows.Count, 3).End(xlUp).Row

For Each c In Sheets("Packs").Range("C4:C" & lr)
If c.Value <> 0 Then
c.Offset(, 1) = ComboBox4.Value
c.Offset(, 2) = ComboBox3.Value
c.Offset(, 3) = TXTCOMMENTS.Value
c.Offset(, 4) = TextBox3.Value
c.Offset(, 5) = TextBox10.Value
End If
Next c

 

[sharky12345]

Now posted elsewhere too;

Paste to row problem

 

[JackDanIce]

What is the purpose of the loop? To me it reads like it's going to paste the same value for each row in columns D-H based on the Userform (and from your code, I can't see how those Userform values are updating inside the loop).

Is this what you are after:

You have a Userform with data, you wish to transfer this data to Sheet Packs. If C12 is the last row with data in it, then you want the User form data to go into D13:H13?

 

[sharky12345]

Thank you.....

I have data in column C and some data in column D.....

I want to paste the userform data into columns D, E, F, G and H but only where there is a value in C and nothing in D.

Does that help?

 

[JackDanIce]

It does, but it's still unclear about the loop. See if this is what you need:

     With Sheets("Packs")
        If .AutoFilterMode Then .AutoFilterMode = False
        LR = .Range("C" & .rows.Count).End(xlUp).row
        
        With .Range("C3:D" & LR)
            .AutoFilter
            .AutoFilter field:=1, Criteria1:="<>"
            .AutoFilter field:=2, Criteria1:="="
        End With
        
        With .Range("D4:D" & LR).SpecialCells(xlCellTypeVisible)
            .value = ComboBox4.value
            .Offset(, 1).value = Combobox3.value
            .Offset(, 2).value = TXTCOMMENTS.value
            .Offset(, 3).value = Textbox3.value
            .Offset(, 4).value = Textbox10.value
        End With
        .AutoFilterMode = False
    End With

 

[sharky12345]

That works perfectly, thanks - the Loop was probably a red herring on my part after trying a number of things.

 

[JackDanIce]

You're welcome, glad it's working now.