Macro - Logical Order

**erock24** · 01-28-2008, 03:22 PM

I am trying to run this macro through my range of data. It seems to work but it doesn't seem to be testing the conditions in the order I have them set up. For example, if you look in the section of "System Checks" a line in my data had S= 03, but instead of giving me "Sys 03..." it gave me "Parts Invoved in Service" because Q = 6501000.
So, How can I get this macro to check for these conditions in the order I have them set up? Or is there a better macro for this.
Thank you...

Please Login or Register  to view this content.

**stevebriz** · 01-28-2008, 03:33 PM

My guess is you will find that if you ´put a break point or exit sub in after the system check you will find it is correct output
I am thinking it might be getting overwitten due to the checks in the EPD section where it checks the value in "Q" & i cell
HTH
Steve

**shg** · 01-28-2008, 04:17 PM

I'd use lookup tables and a formula:

=IFERROR(VLOOKUP(N419, tblAR,2, FALSE),
IFERROR(VLOOKUP(S419, tblSys, 2, FALSE),
IFERROR(VLOOKUP(N419, tblHQ, 2, FALSE),
IFERROR(VLOOKUP(Q419, tblPD, 2, FALSE),
IFERROR(VLOOKUP(R419, tblDept, 2, FALSE),
IFERROR(VLOOKUP(N419, tblLoc, 2, FALSE), "???") ) ) ) ) )

... and this function

Please Login or Register  to view this content.

An example of one of the lookup tables (tblSys) is

Please Login or Register  to view this content.

**shg** · 01-28-2008, 05:03 PM

That could be simplified a little to

=IFERRORS(
VLOOKUP(N419, tblAR, 2, FALSE),
VLOOKUP(S419, tblSys, 2, FALSE),
VLOOKUP(N419, tblHQ, 2, FALSE),
VLOOKUP(Q419, tblPD, 2, FALSE),
VLOOKUP(R419, tblDept, 2, FALSE),
VLOOKUP(N419, tblLoc, 2, FALSE), "???")

... using this function instead:

Please Login or Register  to view this content.

**erock24** · 01-28-2008, 07:00 PM

shg...thank you for the advice...great tip.

Macro - Logical Order

LinkBack

Thread Tools

Rate This Thread

Display

Macro - Logical Order

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions