Offset Range
 

I need a little help with the following code:

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

= problem

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

--
Message posted from http://www.ExcelForum.com

Offset Range
 

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


-----Original Message-----
I need a little help with the following code:

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

= problem

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

Thanks


---
Message posted from http://www.ExcelForum.com/

.

Offset Range
 

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

--

HTH

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

"hotherps " wrote in message
...
I need a little help with the following code:

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

= problem

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

Thanks


---
Message posted from http://www.ExcelForum.com/

Offset Range
 

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

--
Message posted from http://www.ExcelForum.com

Offset Range
 

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

--
Message posted from http://www.ExcelForum.com

Offset Range
 

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"

--
Regards,
Tom Ogilvy



"hotherps " wrote in message
...
That makes sense, except for one thing, it does not work. I have cells
with null values less than 4 cells to the right and the code just keeps
running.

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

Thanks


---
Message posted from http://www.ExcelForum.com/

Offset Range
 

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)

"hotherps " wrote in message
...
Yes we did, and thank you. However I was never able to get it to work. I
still have ranges where 4 or more cells to the right = "" and the code
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 why


---
Message posted from http://www.ExcelForum.com/

Offset Range
 

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.

-----Original Message-----
I need a little help with the following code:

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

= problem

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

Thanks


---
Message posted from http://www.ExcelForum.com/

.

Offset Range
 

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

--
Message posted from http://www.ExcelForum.com

Offset Range
 

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

--
Message posted from http://www.ExcelForum.com

Offset Range
 

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 " wrote in message
...
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 eight
cells that might have slipped in before the criteria.

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

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

Thanks


---
Message posted from http://www.ExcelForum.com/

Offset Range
 

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

:mad:

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


---
Message posted from http://www.ExcelForum.com/