Offset Range

[hotherps]

I need a little help with the following code:

If .Cells(x, y).Value = "." _And counter < 8 _
And need > 0 Then
.Cells(x, y).Value = "ENG"

I'm trying to check to see if the next four cells to the right have th
value "." in them.
I can't seem to get it right, does Offset work differently when yo
want to check a range as opposed to a cell?

Thank

 

[Frank Kabel]

Hi
your current formula only checks if the cell 4 columns to
the right has a dot in it. It does NOT check we other 3
cells to the right

 

[Bob Phillips]

Haven't we been here before. We suggested
if worksheetfunction.countif(activecell.offset(0,1).resize(1,4),".") = 4

--

HTH

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

 

[hotherps]

That makes sense, except for one thing, it does not work. I have cell
with null values less than 4 cells to the right and the code just keep
running.

I'd like to be able to tell it to check all 4 and then fill/or not fil
all of the cells based on the criteria.

Thank

 

[hotherps]

Yes we did, and thank you. However I was never able to get it to work.
still have ranges where 4 or more cells to the right = "" and the cod
keeps running.

Let's say I have an eight cell range

It would look like

. . . "" "" "" "" ""

The result should be :

. . . "" "" "" "" ""

I'm getting :

Eng Eng Eng . "" "" "" ""

Or Eng Eng "" "" "" "" "" ""

Can't figure out wh

 

[Tom Ogilvy]

If .Cells(x, y).Value = "." _
And Application.Countif(Cells(x, y) _
.Offset(0, 1).Resize(1,4),".") = 4 _
And counter < 8 _
And need > 0 Then
Cells(x, y).Value = "ENG"

 

[Bob Phillips]

Is this what you want?

If WorksheetFunction.CountIf(.Cells(x, y).Resize(1, 4), ".") = 4 _
And counter < 8 _
And need > 0 Then _
Cells(x, y).Resize(1, 4).Value = "ENG"
--

HTH

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

 

[dude.]

I assume that the procedure below is nested in a with
statement? eg:

With Activesheet

my procedure

End with

If so and code below is an exact extract - I note that you
are missing a period "." in front of "Cells(x, y).Offset
(0, 4).Value" = "." line. If when running your code the
sheet you are testing the data for is not the activesheet -
then you will not get the result your are looking for!

Not sure if this is your problem but hope helps.

 

[hotherps]

Thanks Tom, I thought it was not working at first, but then I realize
that what I asked you for was incorrect (usually what happens).

The code is doing what I asked, however thats not what I need. If th
condition is true I want to set the previous cells that were alread
filled in with "."

What I asked for:

Eng Eng Eng Eng "" "" "" "" (which you gave me)

What I need :

"." "." "." "." "." ''." "." "."

If the condition is true it should clear all eight cells in the group
not just the ones to the right.

Sorry about tha

 

[hotherps]

Hey Dude, thanks for pointing that out.

Here is what I have left :

If Application.CountIf(Cells(x, y) _
.Offset(0, 1).Resize(1, 8), ".") = 8 _
And .Cells(x, y).Value = "." _
And counter < 8 _
And need > 0 Then
.Cells(x, y).Value = "ENG"
counter = counter + 1
need = need - 1
End If


All I need to do now (Thanks to Tom) is replace any "Eng"s in the eigh
cells that might have slipped in before the criteria.

Bob's forumla would probably have worked also, I just did not realiz
what was happening.

I just want to set all 8 cells back to "." if it does not meet th
criteria, so it's either all "Eng" or all "."

Thank

 

[Bob Phillips]

8? 4? 4 off?

Is this what you mean

If Application.CountIf( _
Cells(x, y).Offset(0, 1).Resize(1, 8), ".") = 8 _
And Cells(x, y).Value = "." _
And counter < 8 _
And need > 0 Then
Cells(x, y).Offset(0, 1).Resize(1, 8).Value = "ENG"
counter = counter + 1
need = need - 1
Else
Cells(x, y).Offset(0, 1).Resize(1, 8).Value = "."
End If



--

HTH

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

 

[hotherps]

Now it's doing this :
ENG . ENG . ENG . ENG .
.. . . . . . . .
.. . . . . . . .
ENG . ENG . ENG . ENG .



It needs to be one or the other out of the following:

ENG ENG ENG ENG ENG ENG ENG ENG
or
.. . . . . . . .

If it meets the criteria it places one of the above into the next 8
cells. Or if it does not meet the criteria it can just skip to the ext
row, but if it created and partial cells i.e. ENG ENG ENG(3)
They need to be set back to .

I tried the exact code you have here because it makes perfect sense....
will not work.



With Sheet236
For x = 11 To 298
counter = 0
did = False
For n = 1 To timeStart
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 = timeStart To timeStart + 7
If Application.CountIf(.Cells(x, y).Offset(0, 1).Resize(1, 8), ".") = 8
_
And .Cells(x, y).Value = "." _
And counter < 8 _
And need > 0 Then
..Cells(x, y).Offset(0, 1).Resize(1, 8).Value = "ENG"
counter = counter + 1
need = need - 1
Else
..Cells(x, y).Offset(0, 1).Resize(1, 8).Value = "."
End If
Next y
End If
Next x
End With