Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

I am new to VBA coming from Excel. In Excel AND can handle up to 30

items. After checking VBA help I see that AND can handle only two

items. Is there a way to write this to arrive at the same results?

Dim TopB As Single

Dim MaxQ As Single

MaxQ = 1

If ltpB <> "" And ltqB <> "" _

And ltpB <> TopB And ltqB > MaxQ Then

Did you try it?????????????????????

I have found that it helps me to write my functions in real words --

test that both ltpB and ltqB are empty strings, if so then check to make

sure that ltpB is not equal to TopB AND ltqB is not greater than MaxQ.

You may want to nest the two functions much like you would do in an

Excel Function, first testing IF both ltpB and ltqB are not empty

strings then test for IF ltpB is not equal to TopB and ltqB is not

greater than Max Q do something.

Dim TopB As Single

Dim MaxQ As Single

MaxQ = 1

If ltpB <> "" And ltqB <> "" Then

If ltpB <> TopB And ltqB > MaxQ Then

DO SOMETHING

Else

DO SOMETHING ELSE

End If

Although Help says: "Used to perform a logical conjunction on two

expressions." there's nothing to stop you stringing several ANDs together.

To test, I tried the following: Enter 1,2,3,4,5 in cells A1:E1

respectively.

1. This formula in another cell:

=AND(A1=1,B1=2,C1=3,D1=4,E1=5)

returns TRUE

2. In vba:

If Range("A1") = 1 And Range("B1") = 2 And Range("C1") = 3 And

Range("D1") = 4 And Range("E1") = 5 Then MsgBox "True!"

(That's all one line) pops up the message box.

3. If you want to stick with the way the worksheet AND works then also

in vba:

If Application.WorksheetFunction.And(Range("A1") = 1, Range("B1") = 2,

Range("C1") = 3, Range("D1") = 4, Range("E1") = 5) Then MsgBox "True too!"

(Likewise one line) also pops up the message box.

If I changed any value in any of the cells A1:E1,

Item 1 returned FALSE, items 2 and 3 did not pop up a message box.

All of your suggestions and

guidance have been very helpful. Rhonda's suggestion helped me

understand the difference between a Block If and a single-line form

If and find mistakes I was making there. David and Pascal, your

suggestions helped me understand that my mistake was not with AND but

with the scope of my variable. Everytime I was cycling through my

function my variable was losing its value so of course the AND was

not doing what I wanted it to.

Didn't find what you were looking for?
Find more on AND - Only two expressions
Or get search suggestion and latest updates.

Related Topics:

- type casting /converting seconds to a days hours minutes seconds
- Binary tree for algebraic expression
- Increment & Decrement Operator in Expressions
- Regular expression
- Regular expressions
- regular expression object in java
- Regular expression help
- Converting Expression from postfix to infix
- Write a class Time that has three data member hour,minutes and seconds.
- Whether the number is the power of two or not.
- Vlookup between two sheets with in a workbook to pick latest value via VBA
- Change every second column to a negative number
- MERGE TWO SHEET IN ONE NEW SHEET
- Insert missing date/merge two spreadsheets
- compare data in two sheets
- vlookup with two imputs
- help: Difference between two arrays
- Accept String input and only display the second character of the words
- accept sting and display second character of string
- Last record is repeated two times
- Two ResultSet pointing to a table
- how do i close a second jframe?
- Transferring files between two PC's
- find the number of working days (monday to firday) between two give
- Time capture of less than one second