Is this imposible for VBA to perform?

[Guest]

I have several variables. First I have a named range "notelist", which is
column B or 2. I want to pick up certain data in the 'offset' columns 12, 17,
18, 19, and 23 if certain conditions are met in columns 2 , 5 and 22. I was
trying to use offset(0,12) etc.

For example, the first criteria is in column 22 if it is "FO" or 'FR" then
go to offset (0,2) if it has a number in the range of 600-606 go to offset(0,
5) if it has a number in the range of 0-150 go to offset(0,17), I need it to
count how many, got to offset(0,18) count how many and offset (0,19) and
count how many have numbers in each column >1, the remainder will have 0 and
I don't want them. The next thing I need it to do is go to column 12 if it
has a letter "X" count how many rows have X's. Finally go to column 23 and
how many are not blank, or how many have letters. I tried to put >A in my
code or >"" but couldn't get any to work. Any ideas?

I have to repeat this several times according to columns 2 and 5. I have
ranges in both that I have to sepeate. For example I have a range 300-360 in
offset(0,2) with a range in offset(0,5) of 151-159. This is repeated several
times with different ranges. I tried use Case 600-606 and then I was going to
change it for the other ranges.

I would put the code I wrote and am trying to run but I think it is pretty
bad. I am a novice and have been battling this for a couple of days.

Any help would be appreciated!
Marie

 

[Guest]

i would look at Select Case to evaluate the variables in columns 2, 5, 22

for instance
sub yourname

do until activecell.row = 500
'this will loop


col2 = activecell.offset(activecell.row, 2).value
col5 = activecell.offset(activecell.row, 5).value
col22 = activecell.offset(activecell.row, 22).value

select case col2

case "FO", "FR"
'write code here

select case col2

case 600 to 606
'this will return true for all cases with the numbers listed above

select case col5

case 0 to 150

'write code here; suggestions are listed below



end select
end select
end select
activecell.offset(1,0).select
loop

end sub

I'm not sure what you want to happen in the 0-150, col 5

you said you need :"t to count how many" How many what? characters?
if you want to count characters you can say col17 =
len(cells(activecell.row, 17))
and do the same for 18 & 19

to count the numbers in a column, you can use the excel formula count

numofValues = application.worksheetfunction.count(enter range)

to count "X"s

numofX = application.worksheetfunction.countif(range,"=X")


This code won't do anything if first column values don't equal "FO" or "FR"