how do i write an if/and/then statement to compare adjacent cells? TIA greatly

[JasonK]

i need to run a for/next loop for a selection which i started like
this:

Range("f3:l9").Select
For Each i In Selection

i need to compare the data in f3 with the date in g3 and h3, then f3
with the two cells above it, then the two cells below it and the two
cells to the left of it, for four different comparisions for each cell
in the selection..

if i.value = 1 and (next cell right) = 1 and (two cells right) = 0
then c=c+1

if i.value = 1 and (next cell up) = 1 and (two cells up) = 0 then
c=c+1
etc....

do i use OFFSET to look at the next cell? if so, how do i write that?

also, after "THEN c=c+1" , i need to take a value exactly 9 cells
below the selected cell and copy that value to another variable. Do I
use OFFSET for that?

if the 3 statements are true, then c=c+1 AND x = (value of 9 cells
below f3 + a constant).

I hope this is clear.
I appreciate your help. Everyone in this group has been phenomenal.
Thanks again,
Jason K.

 

[Bob Phillips]

Not totally clear, but here is a start

For Each i In Range("F3:F9")
If i.Value =1 And i.Offset(0,1).Value = 1 And i.Offset(0,2).Value =
0 Then _
c = c + 1
newVar = c + i.Offset(9,0).Value
End If
Next i

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[paul.robinson]

Hi
Offset will do.

e.g
Dim cell as range
For each cell in Range("F3:L9")
With cell
If .Value = 1 and .offset(0,1).value = 1 and .offset(0, 2).Value = 0
then
c = c+1
end if
end with
next cell

to go one to the right use .offset(0, -1)
to go one down use .offset(1, 0)
to go one up use .offset(-1, 0)

you can also use offset to take you out of your range.

regards
Paul

 

[paul.robinson]

oops, typo
to go one to the LEFT use .offset(0, -1)

regards
Paul

 

[JasonK]

Thank you Paul -
i thought offset was the way, just didn't know how to do it.
thanks again,
jasonK.

 

[JasonK]

thank you Bob.
I appreciate the help.
jasonK

 

[JasonK]

thanks again you guys, i got it running in just a few minutes.
i still need more help with some other things, but i'm going to try to
work them out on my own first.

i'm jazzed.

thanks again,
jasonK

 

[Bob Phillips]

Start a new thread for that Jason, then we can ditch this one.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)