Increment through range with VBA

[Trebor8484]

Hi,

The code below is supposed to iterate through a range, so x starting from row 16 through to 25 and y to control the column increment from 5 to 13, i.e. E to M.

The line highlighted in red is incorrect, can someone advise how this should be written or a different approach please?

For reference, it is always columns E:M and rows 16 to 25.

For y = 5 To 13
        x = 16
        FailCount = 0
[COLOR=#ff0000]        For Each c In sht.Range(Cells(x), Cells(0, y))[/COLOR]
            If c.Value = "N" Then
                FailCount = FailCount + 1
            End If
        Next c
        If FailCount > 0 Then
            'rest of my code here
        End If
        x = x + 1
    Next y

Thanks

 

[Fluff]

Do you want to loop through all cells before checking "FailCount", or run some other code every time a cell = "N"?

 

[Trebor8484]

I need to record the value of FailCount for each row of data so basically each iteration of the y loop and reset it to zero between each column.

The rest of my code then summarises FailCount for each column.

 

[Fluff]

In that case, how about

   For y = 5 To 13
      failcount = 0
      For x = 16 To 25
         If Cells(x, y) = "N" Then failcount = failcount + 1
      Next x
      If failcount > 0 Then
      'rest of my code here
      End If
   Next y

 

[Norie]

Couldn't you use Application.CountIf for each column?

 

[Trebor8484]

Thanks Fluff works great.

Norie, suppose I could have done that with individual statements for each column unless there is a more elegant way.

 

[Norie]

Where are you putting the results?