|
how do i write an if/and/then statement to compare adjacent cells? TIA greatly
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. |
how do i write an if/and/then statement to compare adjacent cells? TIA greatly
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) "JasonK" wrote in message ... 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. |
how do i write an if/and/then statement to compare adjacent cells? TIA greatly
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 JasonK wrote: 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. |
how do i write an if/and/then statement to compare adjacent cells? TIA greatly
oops, typo
to go one to the LEFT use .offset(0, -1) regards Paul wrote: 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 JasonK wrote: 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. |
how do i write an if/and/then statement to compare adjacent cells? TIA greatly
|
how do i write an if/and/then statement to compare adjacent cells? TIA greatly
thank you Bob.
I appreciate the help. jasonK On Thu, 21 Sep 2006 09:51:25 +0100, "Bob Phillips" wrote: 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 |
how do i write an if/and/then statement to compare adjacent cells? thanks Paul & Bob - worked perfectly
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) "JasonK" wrote in message ... 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 On 21 Sep 2006 02:00:14 -0700, wrote: oops, typo to go one to the LEFT use .offset(0, -1) regards Paul wrote: 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 JasonK wrote: 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. |
| All times are GMT +1. The time now is 10:54 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com