CODEWITHSUNDEEP
excelexcel-2010vba
Eudes
Eudes

Reputation: 7

Autofill increments when given a string

I've beeing searching around for quite a while and trying.

What I want to do, is basically an auto-fill that only increments when it finds a value of "02:00" on the column F

1   00:15 
1   00:45 
1   01:00 
1   01:15 
1   01:30 
1   01:45 
1   02:00  -
2   00:15 
2   00:45 
2   01:00 
2   01:15 
2   01:30 
2   01:45 
2   02:00 -
3   00:15 
3   00:45 
3   01:00 
3   01:15 
3   01:30 
3   01:45 
3   02:00 

The code I've does it almost right but always end up filling the column with the last value of the iterator.

    'Days :D
    i = 0
    For Each c In Range("F57:F77")
        For Each x In Range("E57:F77")
            x.Value = i 
            If c.Value = "02:00 " Then
                i = i + 1
                If i >= 4 Then
                    'Exits when overlap
                    Exit Sub
                End If
                Debug.Print i
            End If
        Next
    Next

Upvotes: 0

Views: 42

Answers (2)

user4039065
user4039065

Reputation:

Have you considered using a native worksheet function like COUNTIF for a bulk operation?

With Worksheets("Sheet7")
    With .Range("F57", .Cells(Rows.Count, "F").End(xlUp))
        .Offset(0, -1).Formula = "=COUNTIF(F$57:F57, ""02:00"")+1"
        .Cells = .Value
    End With
End With

Upvotes: 2

Gary's Student
Gary's Student

Reputation: 96753

How about:

Sub qwerty()
   Dim x As Long, r As Range
   x = 1

   For Each r In Range("F57:F77")
      r.Offset(0, -1).Value = x
      If r.Text = "02:00" Then x = x + 1
   Next r
End Sub

enter image description here

Upvotes: 0

Related Questions