Problem with if statement

[Guest]

I thought this should be pretty simple. I basically just want a count of how
many meet a certain criteria in a column. But I am getting really weird
results!

I wrote the following code to pick up only information if there was an FO or
FR in column 22 and if column 5 had a range of 0-150, if all this critereia
is met I want it to go to column 17 and count how many in this column show a
number greater than 1. The next if statement says if colum 18 has a number >1
count how many, the same for column 19, again another if statement. The next
two if statements say if there is an X in column 12 count how many - this
one is not working at all. I want the next if statement to count any cells
that are not blank. The next if doesn't work either, I am trying to get it
to pick up anything that isn't blank in the column, there could be numerous
letters so I thought that would be the easist way.

Do I have to many if statements? The first three are doubling the numbers or
worse. I am getting bigger numbers every time I run it. What am I doing
wrong?


Dim 150DEL30, 150Del60, 150DelNA, 150CO As Long

For Each cell In Range("NoteList")

If cell.Offset(0, 22) = "FO" Or cell.Offset(0, 22) = "FR" Then
If cell.Offset(0, 5) > "00" And cell.Offset(0, 5) < "150" Then
Select Case cell.Offset(0, 2)
Case 600 To 606

If cell.Offset(0, 17) >= 1 Then
150DEL30 = 150DEL30 + 1
End If
If cell.Offset(0, 18) >= 1 Then
150DEL60 = 150DEL60 + 1
End If
If cell.Offset(0, 19) >= 1 Then
150DEL90 = 150DEL90 + 1
End If
If cell.Offset(0, 12) = "X" Then
150NA = 150NA + 1
End If
If cell.Offset(0, 23) <> " " Then
150CO = 150CO + 1
End If
End Select
Next Cell

 

[Fredrik Wahlgren]

I thought this should be pretty simple. I basically just want a count of how
many meet a certain criteria in a column. But I am getting really weird
results!

I wrote the following code to pick up only information if there was an FO or
FR in column 22 and if column 5 had a range of 0-150, if all this critereia
is met I want it to go to column 17 and count how many in this column show a
number greater than 1. The next if statement says if colum 18 has a number
1
count how many, the same for column 19, again another if statement. The next
two if statements say if there is an X in column 12 count how many - this
one is not working at all. I want the next if statement to count any cells
that are not blank. The next if doesn't work either, I am trying to get it
to pick up anything that isn't blank in the column, there could be numerous
letters so I thought that would be the easist way.

Do I have to many if statements? The first three are doubling the numbers or
worse. I am getting bigger numbers every time I run it. What am I doing
wrong?


Dim 150DEL30, 150Del60, 150DelNA, 150CO As Long

For Each cell In Range("NoteList")

If cell.Offset(0, 22) = "FO" Or cell.Offset(0, 22) = "FR" Then
If cell.Offset(0, 5) > "00" And cell.Offset(0, 5) < "150" Then
Select Case cell.Offset(0, 2)
Case 600 To 606

If cell.Offset(0, 17) >= 1 Then
150DEL30 = 150DEL30 + 1
End If
If cell.Offset(0, 18) >= 1 Then
150DEL60 = 150DEL60 + 1
End If
If cell.Offset(0, 19) >= 1 Then
150DEL90 = 150DEL90 + 1
End If
If cell.Offset(0, 12) = "X" Then
150NA = 150NA + 1
End If
If cell.Offset(0, 23) <> " " Then
150CO = 150CO + 1
End If
End Select
Next Cell

I see two problems which may not be relared tio ytour question. The Dim
statement does not work as you may think. Only 150DEL30 is declared as lingm
the rest as variant. You should do this.

Dim 150DEL30 As Long
Dim 150Del60 As Long
Dim 150DelNA As Long
Dim 150CO As Long

Why do you write this:

If cell.Offset(0, 5) > "00" And cell.Offset(0, 5) < "150" Then

instead of

If cell.Offset(0, 5) > 0 And cell.Offset(0, 5) < 150 Then

/Fredrik

 

[JE McGimpsey]

Small correction...

only 150CO is declared as long.

 

[Fredrik Wahlgren]

Small correction...

only 150CO is declared as long.


<snip>

Only 150DEL30 is declared as lingm the rest as variant.

[/QUOTE]

Right. I always forget ...

/fredrik

 

[Guest]

So I need to list each one as long?

What about the rest of the code. I am still trying and it is still doubling
the numbers. I tried changing the if statement's to elseif and that didn't
seem to change anything. It will not pick up any of the X's in the columns.
Do I need to use different than 'long' for them? It still isn't picking up
anythin in the column that <> "". Somebody please hlep!

Small correction...

only 150CO is declared as long.


<snip>

Only 150DEL30 is declared as lingm the rest as variant.

[/QUOTE]

 

[Fredrik Wahlgren]

So I need to list each one as long?

What about the rest of the code. I am still trying and it is still doubling
the numbers. I tried changing the if statement's to elseif and that didn't
seem to change anything. It will not pick up any of the X's in the columns.
Do I need to use different than 'long' for them? It still isn't picking up
anythin in the column that <> "". Somebody please hlep!

Today is Friday...

I haven't tried your code. You should use the debugger to see what it does.
I think that you only expect one of the IF statements to run within each
loop. If so, you should call Next Cell at the end of each IF statement.

/Fredrik

 

[Guest]

Thanks, but I am in a bigger mess than I was. Now it is saying something
about block if without end if. I have put end if behind every if statement.
I have spent two days on this. I am about ready to go nuts.

 

[JE McGimpsey]

That will give you a syntax error. There can only be one Next for every
For.

 

[JE McGimpsey]

This seems like it would be rather easy to do with worksheet functions.
Do you have to use VBA?

However, I may just be dense, but from your description, I can't tell
exactly how your data is laid out, and what you're trying to do vs.
what's actually happening...Perhaps some amplification would help.

 

[Guest]

I have to do it in VB because of the amount of data in the file. I will try
to explain better what I need with my code. I have a huge file with info
about accounts payable. The first thing that I need to find is if the note
has an FO or FR in column 22 if it does than I need to look at what type note
it was. The first types I am checking are types 600-606. If it has the FO and
it is a 600 then I need to look at how it was scored. My first group is <150
- 159. So If column 22 has FO or FR and column 2 is a type 600 and column 5
has a figure <150-159 I need to know more...next look and see if it was ever
past due. We have 5 choices. 30 days, 60 days, 90 days, 120 days, or we
charged it off. So if it has FO or FR and it is <150-159 look in the column
that has 30 days if there is a number there count it as one, if there is a
number in 60 days count it as one, 90 count it as one, 120 count it as one,
charged off count it as one. So I want to end with a number for all accounts
with FO and FR in product type 600-606 with a score of <150-159 that have
ever been 30,60,90,120 days past due or charged off. The 30, 60, 90 will
show a number in the column so I put >1 in my code. The 120 will have an X so
I used ="X" in my code - but it didn't work - and the charged off will have a
letter. Up to 10 possibilites so instead of listing all ten I tried <> "" and
it didn't work. I also tried >A, because the first choice would be C but it
didn't work either.
I have 6 other ranges of product types I need to check with additional score
ranges in multiples of 10 (from <150 - 300) so I thought if I could get one
group to work I could just copy the code for all the other ranges. I thuoght
I could change the Case to correspond to the types I needed.

When I run this the 30 and 60 will calculate right but if I clear the sheet
and run it again it doubles it, triples it, ect. I haven't been able to get
the 90, 120 or charge off to pick up anything. I would like to get the
following code to work if it is possible. Any suggestions?


Dim 150DEL30 As Long
Dim 150Del60 As Long
Dim 150DelNA As Long
Dim 150CO As Long

For Each cell In Range("NoteList")

If cell.Offset(0, 22) = "FO" Or cell.Offset(0, 22) = "FR" Then
If cell.Offset(0, 5) > 0 And cell.Offset(0, 5) < 150 Then
Select Case cell.Offset(0, 2)
Case 600 To 606
If cell.Offset(0, 17) >= 1 Then
150DEL30 = 150DEL30 + 1
End If
If cell.Offset(0, 18) >= 1 Then
150DEL60 = 150DEL60 + 1
End If
If cell.Offset(0, 19) >= 1 Then
150DEL90 = 150DEL90 + 1
End If
If cell.Offset(0, 12) = "X" Then
150NA = 150NA + 1
End If
If cell.Offset(0, 23) <> " " Then
150CO = 150CO + 1
End If
End Select
Next Cell

 

[JE McGimpsey]

First, if you have a lot of data in the file, worksheet functions will
almost certainly be faster than VBA. However...

I don't know why you're seeing a doubling or tripling. After cleaning it
up, your code worked for me in a test sheet, except for your comparison
to " ", which is a space character, rather than a comparison to a null
string, "". I don't know how you have variables starting with the number
1 - those are illegal names. BTW - I assume from the varying
capitalization and differing variable names that you're not pasting the
code from your project into your message, so I can't tell if you have
typos.

You should definitely put

Option Explicit

at the top of your module - that will force you to declare your
variables exactly, so if you

Dim n150DelNa As Long

but use

n150NA = n150NA + 1

you'll get a "Variable not defined" error.

Here's what I used, which is functionally equivalent to what I *think*
you're trying to do:

Dim rCell As Range
Dim n150DEL30 As Long
Dim n150DEL60 As Long
Dim n150DEL90 As Long
Dim n150DELNA As Long
Dim n150CO As Long

For Each rCell In Range("NoteList")
With rCell
If .Offset(0, 22).Text Like "F[OR]" Then
If .Offset(0, 5).Value > 0 And .Offset(0, 5) < 150 Then
Select Case .Offset(0, 2)
Case 600 To 606
n150DEL30 = n150DEL30 - (.Offset(0, 17).Value >= 1)
n150DEL60 = n150DEL60 - (.Offset(0, 18).Value >= 1)
n150DEL90 = n150DEL90 - (.Offset(0, 19).Value >= 1)
n150DELNA = n150DELNA - (.Offset(0, 12).Value = "X")
n150CO = n150CO - (.Offset(0, 23).Text <> "")
End Select
End If
End If
End With
Next rCell
Debug.Print n150DEL30, n150DEL60, n150DEL90, n150DELNA, n150CO

Note the use of - rather than +. VBA's "True" value is coerced to -1 in
a math operation, rather than +1 as it is in XL.

 

[Guest]

Thank you so very, very much. I changed it this afternoon and it worked.
I was just wondering about something, the girl who showed me what little I
do know about VBA always used the cell.offset (x,x) instead of the way you
had .offset. What is the difference and why use one over the other? Does it
pertain to the rcell at the beginning? She also was the one who told me to
do the +1 to count.
Thanks for your help!
Marie

First, if you have a lot of data in the file, worksheet functions will
almost certainly be faster than VBA. However...

I don't know why you're seeing a doubling or tripling. After cleaning it
up, your code worked for me in a test sheet, except for your comparison
to " ", which is a space character, rather than a comparison to a null
string, "". I don't know how you have variables starting with the number
1 - those are illegal names. BTW - I assume from the varying
capitalization and differing variable names that you're not pasting the
code from your project into your message, so I can't tell if you have
typos.

You should definitely put

Option Explicit

at the top of your module - that will force you to declare your
variables exactly, so if you

Dim n150DelNa As Long

but use

n150NA = n150NA + 1

you'll get a "Variable not defined" error.

Here's what I used, which is functionally equivalent to what I *think*
you're trying to do:

Dim rCell As Range
Dim n150DEL30 As Long
Dim n150DEL60 As Long
Dim n150DEL90 As Long
Dim n150DELNA As Long
Dim n150CO As Long

For Each rCell In Range("NoteList")
With rCell
If .Offset(0, 22).Text Like "F[OR]" Then
If .Offset(0, 5).Value > 0 And .Offset(0, 5) < 150 Then
Select Case .Offset(0, 2)
Case 600 To 606
n150DEL30 = n150DEL30 - (.Offset(0, 17).Value >= 1)
n150DEL60 = n150DEL60 - (.Offset(0, 18).Value >= 1)
n150DEL90 = n150DEL90 - (.Offset(0, 19).Value >= 1)
n150DELNA = n150DELNA - (.Offset(0, 12).Value = "X")
n150CO = n150CO - (.Offset(0, 23).Text <> "")
End Select
End If
End If
End With
Next rCell
Debug.Print n150DEL30, n150DEL60, n150DEL90, n150DELNA, n150CO

Note the use of - rather than +. VBA's "True" value is coerced to -1 in
a math operation, rather than +1 as it is in XL.

I have to do it in VB because of the amount of data in the file. I will try
to explain better what I need with my code. I have a huge file with info
about accounts payable. The first thing that I need to find is if the note
has an FO or FR in column 22 if it does than I need to look at what type note
it was. The first types I am checking are types 600-606. If it has the FO and
it is a 600 then I need to look at how it was scored. My first group is <150
- 159. So If column 22 has FO or FR and column 2 is a type 600 and column 5
has a figure <150-159 I need to know more...next look and see if it was ever
past due. We have 5 choices. 30 days, 60 days, 90 days, 120 days, or we
charged it off. So if it has FO or FR and it is <150-159 look in the column
that has 30 days if there is a number there count it as one, if there is a
number in 60 days count it as one, 90 count it as one, 120 count it as one,
charged off count it as one. So I want to end with a number for all accounts
with FO and FR in product type 600-606 with a score of <150-159 that have
ever been 30,60,90,120 days past due or charged off. The 30, 60, 90 will
show a number in the column so I put >1 in my code. The 120 will have an X so
I used ="X" in my code - but it didn't work - and the charged off will have a
letter. Up to 10 possibilites so instead of listing all ten I tried <> "" and
it didn't work. I also tried >A, because the first choice would be C but it
didn't work either.
I have 6 other ranges of product types I need to check with additional score
ranges in multiples of 10 (from <150 - 300) so I thought if I could get one
group to work I could just copy the code for all the other ranges. I thuoght
I could change the Case to correspond to the types I needed.

When I run this the 30 and 60 will calculate right but if I clear the sheet
and run it again it doubles it, triples it, ect. I haven't been able to get
the 90, 120 or charge off to pick up anything. I would like to get the
following code to work if it is possible. Any suggestions?


Dim 150DEL30 As Long
Dim 150Del60 As Long
Dim 150DelNA As Long
Dim 150CO As Long

For Each cell In Range("NoteList")

If cell.Offset(0, 22) = "FO" Or cell.Offset(0, 22) = "FR" Then
If cell.Offset(0, 5) > 0 And cell.Offset(0, 5) < 150 Then
Select Case cell.Offset(0, 2)
Case 600 To 606
If cell.Offset(0, 17) >= 1 Then
150DEL30 = 150DEL30 + 1
End If
If cell.Offset(0, 18) >= 1 Then
150DEL60 = 150DEL60 + 1
End If
If cell.Offset(0, 19) >= 1 Then
150DEL90 = 150DEL90 + 1
End If
If cell.Offset(0, 12) = "X" Then
150NA = 150NA + 1
End If
If cell.Offset(0, 23) <> " " Then
150CO = 150CO + 1
End If
End Select
Next Cell

 

[JE McGimpsey]

Using the

.Offset(....)

uses the

With...End With

syntax. The "." indicates that what follows refers to the object
specified in "With x", so

With rCell
If .Offset(0, 22).Text Like "F[OR]" Then
If .Offset(0, 5).Value > 0...

is equivalent to

If rCell.Offset(0, 22).Text Like "F[OR]" Then
If rCell.Offset(0, 5).Value > 0...

 

[Guest]

Thanks again, you have helped me so much!

Marie

Using the

.Offset(....)

uses the

With...End With

syntax. The "." indicates that what follows refers to the object
specified in "With x", so

With rCell
If .Offset(0, 22).Text Like "F[OR]" Then
If .Offset(0, 5).Value > 0...

is equivalent to

If rCell.Offset(0, 22).Text Like "F[OR]" Then
If rCell.Offset(0, 5).Value > 0...

Thank you so very, very much. I changed it this afternoon and it worked.
I was just wondering about something, the girl who showed me what little I
do know about VBA always used the cell.offset (x,x) instead of the way you
had .offset. What is the difference and why use one over the other? Does it
pertain to the rcell at the beginning? She also was the one who told me to
do the +1 to count.