in each row count same alphabet in cells , as soon as it counts 6 next cell marked as WO

[kshitij_dch]

Hello All,
I am working on one logic , need help
i have a range Range A:AE
Have Certain codes (alphabets) on cells like "P" , "SL" , "TL" , "MK"
i want to apply logic that starting from cell A1 or A2 or A3......


macro should check for code "P" in cells , as soon as it finds 6 "P" in a row (No Gap), next cell should automatically marked as "WO".


Please suggest ....here is what i want

P

p

p

p

p

p

WO

p

p

p

p

p

p

p

p

p

WO

<colgroup><col width="64" span="13" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[gallen]

Are you looking for a formula or VBA?

 

[kshitij_dch]

if their is a formula it will do or VBA it will also do for me ?

 

[gallen]

Can't think of formula but this VBA would do it (test on copy of data)

Sub SetWO()
Dim lr As Long 'last row
Dim r As Long, c As Long ' row and column counters
Dim cn As Integer 'counter for 'p'
    
    'get last used row in column A
    lr = Range("A" & Rows.Count).End(xlUp).Row
    
    'loop through all rows
    For r = 1 To lr
        'loop through all columns from A to AE
        For c = 1 To 31
            'if current cell is 'P' then add to counter
            If UCase(Cells(r, c)) = "P" Then
                cn = cn + 1
                'if we have 6 ps in a row next cell is WO
                If cn = 6 Then
                    Cells(r, c + 1) = "WO" 'QUESTION: What if there is a 7th 'P'?
                    cn = 0
                End If
            Else
                cn = 0
            End If
        Next c
    Next r
End Sub

 

[kshitij_dch]

This is working perfectly !! Thanks Gallen

If their is a 7th P in a row ideally macro should replace 7th P with WO ? it it possible ?

 

[gallen]

The code already does that

 

[gallen]

Just noticed a potential bug, dependant on how you want it to work. If for example the last 3 columns of row 1 are 'P' and the first 3 columns of row 2 are 'P' then then fourth column will be "WO"

If you need to reset the counter for each row just put cn = 0 beneath the 'For r = 1 to lr' line

 

[kshitij_dch]

Thanks a lot you are Great Help !!