Add an exception rule

H

hotherps

Here is the code I am using.


If .Cells(x, y).Value = "." _
And counter < 6 _
And need2 > 0 Then
.Cells(x, y).Value = "Test"


Is there a way I can have the code look forward down the row to see i
it can be placed 6 times?

There are many reasons it could come up short, and I'm trying to avoi
having an incomplete row. I want the value to appear 6 times or not a
all.

I would prefer the code to just continue on through the rows until i
can add all 6 cells again.

Thank
 
B

Bob Phillips

Hotherps,

You could use Countif to check the row

If worksheetfunction.countif(Rows(1),".") >= 6 then
...

--

HTH

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

hotherps

could not get that to work.

Tried this (ugly)

If .Cells(x, y).Value = "." _
And counter < 6 _
And need > 0 And _
.Cells(x, y).Offset(0, 6).Value = "." And _
.Cells(x, y).Offset(0, 5).Value = "." And _
.Cells(x, y).Offset(0, 4).Value = "." And _
.Cells(x, y).Offset(0, 3).Value = "." And _
.Cells(x, y).Offset(0, 2).Value = "." And _
.Cells(x, y).Offset(0, 1).Value = "." Then
.Cells(x, y).Value = "ENG"
counter = counter + 1

almost worked but when cells below the range were null it threw off th
row
 
B

Bob Phillips

This works for me

If WorksheetFunction.CountIf(.Range(.Cells(x, y), .Cells(x, y + 6)),
".") = 7 _
And counter < 6 _
And need > 0 Then
Cells(x, y).Value = "ENG"
counter = counter + 1
End If

even with empty cells

--

HTH

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

hotherps

Thanks Bob it does work, however I overlooked something. If the valu
"One" or "Two" is located I have to perform the same test.

So if ".", "One" or "Two" is in the next 6 cells. Then that row shoul
be skipped. It should move down one row and begin again.

Do you know how I would do this?

Thank
 
T

Tom Ogilvy

As written, it requires that "." be in each of the 6 cells plus the original
cell. You now seem to say to skip it if any of the 6 cells contain "." or
"one" or "two". What conditions actually have to be met to perform the
action?
 
H

hotherps

It's kind of lenghty but I''l post the whole sub below. Basically wha
I'm trying to do is to ensure the that the cells remain "." if th
condition can not be applied to all 8 cells.

You can see where I tried the " If WorksheetFunction.CountIf(.
statement" but it still failed in some circumstances. Meaning tha
"One" and two had blended within the eight cells.

I want to make a match on all 8 cells or show the value "." in it'
place.

Like This
OneOneOneOneOneOneOneOne
Not This
OneOneOneTwoTwoTwoTwoTwo

If it Fails
OneOneOne . . . .or
Two Two Two . . . etc.

Thanks




Sub Tasks()
Dim need As Integer
Application.ScreenUpdating = False
skillx = 9
timeStart = -1
timeStart2 = 1
For period = 1 To 12
timeStart = timeStart + 8
timeStart2 = timeStart2 + 6
For skilly = 104 To 124
skillNam = Sheet236.Cells(skillx, skilly).Value
need = Sheet236.Cells(period + 318, skilly - 97).Value
Sheet236.Cells(period + 304, skilly - 97).Value
If skillNam = "ENG" Then
Eng period, timeStart2, skilly, need
ElseIf skillNam = "IND" Then
Ind period, timeStart, skilly, need
Else
OtherSkill period, timeStart, skilly, skillNam, need
End If
Next skilly
Next period
Application.ScreenUpdating = True
For Each cell In Range("G11:CX287")
If cell.Value = "." Then
With cell.Offset(0, 0).Interior
.Pattern = xlGrid
.PatternColor = RGB(225, 225, 0)
End With
End If
Next


'MsgBox "Complete"
End Sub

Public Sub Eng(ByVal period As Integer, ByVal timeStart2 As Integer
ByVal skilly As Integer, ByVal need As Integer)
Dim counter As Integer
Dim did As Boolean
Dim n As Integer

With Sheet236
For x = 11 To 298
counter = 0
did = False
For n = 1 To timeStart2
If .Cells(x, n).Value = "ENG" Then
did = True
End If
Next n
If .Cells(x, skilly).Value = "x" And did = False Then
For y = timeStart2 To timeStart2 + 5
If WorksheetFunction.CountIf(.Range(.Cells(x, y), .Cells(x, y
6)), ".") > 1 _
And .Cells(x, y).Value = "." _
And counter < 6 _
And need > 0 Then
.Cells(x, y).Value = "ENG"
counter = counter + 1
need = need - 1
End If
Next y
End If
Next x
End With
pack
End Su
 
Top