loop through looking for value
 

Howdie all.
I have a macro that loops through a series of worksheets and sets an
ascending numeric value in the first column-- offsetting 5 rows for each
subsequent ascending number placement.
There are, from time to time, "Info Only" values in specific cells that we
need to retain. I.e., in A10 is the value- Info, and in A11 is "Only," then
say- 25 rows down is a repeat at A35, and A36. But the placement of Info Only
is random, and cannot be a hard value placement.
What I'm trying to do is to have my macro look through the values, and if it
finds INFO, skip the 5 cell range, and go to the next 5 cell range, where it
places a numeric value.
My thinking was:

for each mycell in myRng
if mycell.value = "info" then
mycell.offset(5,0)
end if
next mycell

The balance of the macro works great, and does exactly what we need it to--
place ascending numeric values in the correct column, and cycle through each
worksheet in the file doing the same on each.
However, I've missed something here as it's not working. I've tried a few
variations, for which I receive 1004, 438, 7, and 91 runtime errors.
In the reading I've done here on the newsgroups, and in the helpfile, I just
keep missing it.

loop through looking for value
 

Unless you left out some code, this isn't going to do anything. Say your
range is A1 to A10, if it finds "info" in say A3, it does the offset and end
if, but the next forces it to the next cell in the range (A4). If you are
wanting to take that offset and do something with it, you would exit for
first.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"SteveDB1" wrote:

Howdie all.
I have a macro that loops through a series of worksheets and sets an
ascending numeric value in the first column-- offsetting 5 rows for each
subsequent ascending number placement.
There are, from time to time, "Info Only" values in specific cells that we
need to retain. I.e., in A10 is the value- Info, and in A11 is "Only," then
say- 25 rows down is a repeat at A35, and A36. But the placement of Info Only
is random, and cannot be a hard value placement.
What I'm trying to do is to have my macro look through the values, and if it
finds INFO, skip the 5 cell range, and go to the next 5 cell range, where it
places a numeric value.
My thinking was:

for each mycell in myRng
if mycell.value = "info" then
mycell.offset(5,0)
end if
next mycell

The balance of the macro works great, and does exactly what we need it to--
place ascending numeric values in the correct column, and cycle through each
worksheet in the file doing the same on each.
However, I've missed something here as it's not working. I've tried a few
variations, for which I receive 1004, 438, 7, and 91 runtime errors.
In the reading I've done here on the newsgroups, and in the helpfile, I just
keep missing it.

loop through looking for value
 

Morning John.
My goal was to have it look for the word-- info-- and then offset if it
found the word, and if it did not find it, to move along to previously coded
processes--- in this case, input the numeric value.


"John Bundy" wrote:

Unless you left out some code, this isn't going to do anything. Say your
range is A1 to A10, if it finds "info" in say A3, it does the offset and end
if, but the next forces it to the next cell in the range (A4). If you are
wanting to take that offset and do something with it, you would exit for
first.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"SteveDB1" wrote:

Howdie all.
I have a macro that loops through a series of worksheets and sets an
ascending numeric value in the first column-- offsetting 5 rows for each
subsequent ascending number placement.
There are, from time to time, "Info Only" values in specific cells that we
need to retain. I.e., in A10 is the value- Info, and in A11 is "Only," then
say- 25 rows down is a repeat at A35, and A36. But the placement of Info Only
is random, and cannot be a hard value placement.
What I'm trying to do is to have my macro look through the values, and if it
finds INFO, skip the 5 cell range, and go to the next 5 cell range, where it
places a numeric value.
My thinking was:

for each mycell in myRng
if mycell.value = "info" then
mycell.offset(5,0)
end if
next mycell

The balance of the macro works great, and does exactly what we need it to--
place ascending numeric values in the correct column, and cycle through each
worksheet in the file doing the same on each.
However, I've missed something here as it's not working. I've tried a few
variations, for which I receive 1004, 438, 7, and 91 runtime errors.
In the reading I've done here on the newsgroups, and in the helpfile, I just
keep missing it.