repeat code until row 24

J

Josh

I am trying to move the contents of column E to column D if column B -
C. The code I have works for one row, I have tried and failed many
times in an attempt to repeat it until row 24. How would I make this
repeat each row until 24. (It starts on 2) I am sure there is an easier
way to do this, but my abilities are lacking.

Private Sub CommandButton1_Click()
If Range("B2") = "C" Then
If Range("E2") <> "" Then
With Range("E2").Copy
Range("D2").PasteSpecial Paste:=xlValues
Range("E2").Clear
End With
End If
End If
End Sub

Thanks,
Josh

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
 
B

Bob Phillips

Why not use worksheet formulae?

D2: = IF(AND(E2<>"",B2="C"),E2,"")

and copy down.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
T

Tom Ogilvy

Private Sub CommandButton1_Click()
for i = 0 to 23
If Range("B2").Offset(i,0) = "C" Then
If Range("E2").Offset(i,0) <> "" Then
Range("E2").Offset(i,0).Copy
Range("D2").Offset(i,0).PasteSpecial Paste:=xlValues
Range("E2").Offset(i,0).Clear
End If
End If
End Sub
 
P

Paulw2k

Hi Josh,
This should do it!

Sub Test()
Dim Rng As Range
Dim cel As Range

Set Rng = Range("E1:E24")

For Each cel In Rng
With cel
If .Offset(0, -3).Value = "C" Then
.Value = .Offset(0, -1).Value
.Offset(0, -1).ClearContents
End If
End With
Next




End Sub


Regards

Paul
 
Top