How to loop through code

[AndrewGKenneth]

Hi there,

I am having a problem with my current bit of code as I am quite new to vba. The code I am having an issue with is shown below.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range ("J3").Value > 0 and Range ("C3").Value = "" Then
Range ("C3").Value = Range("C2").Value
Range ("D3").Value = Range("D2").Value
Range ("E3").Value = Range("E2").Value
Range ("F3").Value = Range("F2").Value
Range ("G3").Value = Range("G2").Value
Range ("H3").Value = Range("H2").Value
Range ("I3").Value = Range("I2").Value
End IF

If Range ("J4").Value > 0 and Range ("C4").Value = "" Then
Range ("C4").Value = Range("C3").Value
Range ("D4").Value = Range("D3").Value
Range ("E4").Value = Range("E3").Value
Range ("F4").Value = Range("F3").Value
Range ("G4").Value = Range("G3").Value
Range ("H4").Value = Range("H3").Value
Range ("I4").Value = Range("I3").Value
End if

And so on ........


I need this to continue all the way down the worksheet but don't currently know how to loop this function. At the moment, I have manually entered the code all the way down to row 150. But have now had the error message Compile Error: Procedure to Large.


Could someone please help me how to code this function to loop using the for next function please?

Thanks so much in advance,
Andrew

 

[Michael M]

Maybe like this....but you haven't restricted the target, so everytime a cell ANYWHERE on the sheet changes, this code will run ??

What cell will trigger the code ??

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lr As Long, r As Long
Application.EnableEvents = False
lr = Cells(Rows.Count, "J").End(xlUp).Row
For r = 3 To lr
    If Range("J" & r).Value > 0 And Range("C" & r).Value = "" Then
        Range("C" & r & ":I" & r).Value = Range("C" & r - 1 & ":I" & r - 1).Value
    End If
Next r
Application.EnableEvents = True
End Sub

 

[AndrewGKenneth]

Hi Michael,

Thanks a lot I will give that a go. It will be column C that will trigger the code, so this could be C3, C4, C5 and so on.

All the best,
Andrew

 

[AndrewGKenneth]

Thanks Michael,

That worked perfectly, much appreciated !!

Andrew

 

[Michael M]

Added an extra line so that if a change is made that is not column "C" then the code will not run

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lr As Long, r As Long
If Target.Column <> 3 Then Exit Sub
Application.EnableEvents = False
lr = Cells(Rows.Count, "J").End(xlUp).Row
For r = 3 To lr
    If Range("J" & r).Value > 0 And Range("C" & r).Value = "" Then
        Range("C" & r & ":I" & r).Value = Range("C" & r - 1 & ":I" & r - 1).Value
    End If
Next r
Application.EnableEvents = True
End Sub

 

[Rick Rothstein]

Thanks a lot I will give that a go. It will be column C that will trigger the code, so this could be C3, C4, C5 and so on.

So is your ultimate goal to copy the previous row from Column C thru Column I whenever a value is deleted or cleared in the range C3:C150 and the value in Column J of that same row is greater than 0?