if_and_or_then loop help

M

mswantek

Board Regular
Joined
Jul 9, 2002
Messages
123
I am trying to add some logic to a particular project I am working on. The form collects data basaed on a users input and then makes decisions based on certain criteria.
I am having some trouble getting a If (condition) and (condition) and (condition) then loop to give me my result. I feel that I am so close but am missing something pretty obvious.
Somebody please help.
The code is reading a excel database for a "true" or "false" input in a series of cells. If all the conditions are fullfilled, it routes the form to a particular user. The problem I am having when I step through the code is that the "and values" are all reading ok, but the "then" result is not tripping the value it should.
Sorry this is so long but I just want people to understand the problem.
code:
Code: 
ElseIf Range("d9").Value = "Fabricate F/G Wheel covers" And _
'((ActiveCell.Offset(0, 20) = "TRUE" Or _
'ActiveCell.Offset(0, 112) = "TRUE") And _
'(ActiveCell.Offset(0, 27) = "TRUE" Or _
'ActiveCell.Offset(0, 114) = "TRUE") And _
'(ActiveCell.Offset(0, 34) = "TRUE" Or _
'ActiveCell.Offset(0, 116) = "TRUE") And _
'(ActiveCell.Offset(0, 41) = "TRUE" Or _
'ActiveCell.Offset(0, 118) = "TRUE") And _
'(ActiveCell.Offset(0, 48) = "TRUE" Or _
'ActiveCell.Offset(0, 120) = "TRUE") And _
'(ActiveCell.Offset(0, 55) = "TRUE" Or _
'ActiveCell.Offset(0, 122) = "TRUE") And _
'(ActiveCell.Offset(0, 62) = "TRUE" Or _
'ActiveCell.Offset(0, 124) = "TRUE") And _
'(ActiveCell.Offset(0, 83) = "TRUE" Or _
'ActiveCell.Offset(0, 130) = "TRUE")) Then
'checked = True
'Exit Sub
If anyone can help I would appreciate it.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
Here is a slightly different approach you may want to consider. Try creating separate IF statements that add a value to a running total, then check your total, i.e.:

MyTotal=0
If Condition 1 then MyTotal=MyTotal + 1
If Condition 2 then MyTotal=MyTotal + 1
...
If Condition 10 then MyTotal = MyTotal + 1
If MyTotal = 10 then checked = True
 
Upvote 0
Are you sure your capitals and smalls are correct - the test will be case sensitive. Fabricate F/G Wheel covers is a long string and your caps and smalls are inconsistent. What you might do is compare Ucase(Range("d9").Value) to "FABRICATE F/G WHEEL COVERS".
 
Upvote 0
The case is ok, because that statement (while stepping throught the loop) show that it is satisfied.
 
Upvote 0
Try Andrew's suggestion too - still it would be a better practice to convert everything to caps when making the comparison.
 
Upvote 0
Can I suggest that you start with just 2 conditions, eg:

Code: 
ElseIf Range("d9").Value = "Fabricate F/G Wheel covers" And _ 
((ActiveCell.Offset(0, 20) = "TRUE" Or _ 
ActiveCell.Offset(0, 112) = "TRUE")

and see if that works. If it does then add the next condition and test again.

If it doesn't then try something like this to debug your code:

Code: 
ElseIf Range("d9").Value = "Fabricate F/G Wheel covers" Then
MsgBox ActiveCell.Offset(0, 20) = "TRUE"
MsgBox ActiveCell.Offset(0, 112) = "TRUE"
MsgBox ((ActiveCell.Offset(0, 20) = "TRUE" Or _ 
ActiveCell.Offset(0, 112) = "TRUE")
End If

It may be that your offsets are wrong.
 
Upvote 0
Andrew Poulsom said:
Another thing ...

Are the cell values Boolean TRUE/FALSE? If so you should omit the quotes.
Click to expand...

I verified and checked the boolean value it is NOT a boolean result itis just a text "TRUE" or "FALSE" result in the cell, so I am keeping the quotes.
 
Upvote 0
Andrew Poulsom said:
Can I suggest that you start with just 2 conditions, eg:

Code: 
ElseIf Range("d9").Value = "Fabricate F/G Wheel covers" And _ 
((ActiveCell.Offset(0, 20) = "TRUE" Or _ 
ActiveCell.Offset(0, 112) = "TRUE")

and see if that works. If it does then add the next condition and test again.

If it doesn't then try something like this to debug your code:

Code: 
ElseIf Range("d9").Value = "Fabricate F/G Wheel covers" Then
MsgBox ActiveCell.Offset(0, 20) = "TRUE"
MsgBox ActiveCell.Offset(0, 112) = "TRUE"
MsgBox ((ActiveCell.Offset(0, 20) = "TRUE" Or _ 
ActiveCell.Offset(0, 112) = "TRUE")
End If

It may be that your offsets are wrong.
Click to expand...

Andrew,
When stepping throught the code (hovering over the statement) the values are showing that they are "True" or "false" its just that when the program hits the THEN statement it skips right over it . That is what is so confusing. It looks as if all the conditions are fullfilled but yet it skips over the part I need to happen.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
How can i extract the correct leave time on this code?
Replies
1
Views
362
rlv01
R
P
  • Solved
Never ending loop with OnTime
Replies
4
Views
207
picklefactory
P
D
  • Question
VBA code works when called directly, but not from another routhine
Replies
7
Views
708
davidepstein22
D
M
  • Question
Multiple users using same user form having errors and slow at times
Replies
2
Views
227
Joe4
Joe4
F
  • Question
How to get optionbutton values from two different groups
Replies
2
Views
244
NoSparks
N

Forum statistics

Threads
1,214,985
Messages
6,122,605
Members
449,089
Latest member
Motoracer88

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top