CODEWITHSUNDEEP
vba
Phoenix Ning
Phoenix Ning

Reputation: 7

how to apply a formula in one cell to the entire column

Column E Column F
Start_Time End_Time
12:16:56 12:16:57
12:16:57 12:16:59
12:18:50 12:19:04
12:20:13 12:20:13
12:20:32 12:20:33
12:20:42 12:20:49
12:31:16 12:31:17
12:37:32 12:37:47
12:45:41 12:45:43
12:48:36 12:48:36
12:48:44 12:48:46

I want to do if the end time is equal to the start time, use the previous end time as the start time. For example, in the picture if E5=F5, then E5=F4. I used this common,it works but only in the specific cell. How can I apply it to the whole column? I tried If...then...statement as following, not works.Thanks. 

For i = 2 To lastrow - 1

  If Range("E3").Value = Range("F3").Value Then
     Range("E3").Value = Range("F2").Value
   End If
Next i

Expected output:

Column E Column F
Start_Time End_Time
12:16:56 12:16:57
12:16:57 12:16:59
12:18:50 12:19:04
12:19:04 12:20:13
12:20:32 12:20:33
12:20:42 12:20:49
12:31:16 12:31:17
12:37:32 12:37:47
12:45:41 12:45:43
12:45:43 12:48:36
12:48:44 12:48:46

Upvotes: 0

Views: 56

Answers (2)

Scott Craner
Scott Craner

Reputation: 152495

No Loop needed:

With Worksheets("Sheet1") ' Change to your sheet name
    .Range("E3:E" & lastrow - 1).Value = _
        .Evaluate("IF(E3:E" & lastrow - 1 & " = F3:F" & lastrow - 1 & ",F2:F" & _
        lastrow - 2 & ",E3:E" & lastrow - 1 & ")")
End With

Upvotes: 1

Ibo
Ibo

Reputation: 4309

For i = 2 To lastrow - 1
  If Range("E" & i+1).Value = Range("F" & i+1).Value Then
     Range("E" & i+1).Value = Range("F" & i).Value
  End If
Next i

Upvotes: 1

Related Questions