formula explanations...

[scotiajoe]

=IF(LEN(RC[-8])<4,CONCATENATE(VLOOKUP(RC5,accounts,2),TEXT(RC[-8],""00"")),RC[-8])


=IF(ISERR(RC[-2]),0,IF(ISNA(RC[-2]),0,IF(EXACT(RC[-6],RC[-1]),1,

Thanks

 

[J.E. McGimpsey]

The first checks the cell 8 columns to the left. If the length of
the text in that cell is greater than or equal to 4, the formula
returns that cell's value. If not, the formula returns a
concatenation of a lookup in the accounts table based on the cell in
column E of the formula's row, and the value in the cell 8 columns
to the left, formatted as two digits.

The second, partial formula, checks the cell 2 columns to the left.
If the value is an error the formula returns 0. Otherwise, if the
value six columns to the left exactly matches the value in the first
column to the left (including Case), it returns 1. If the comparion
isn't exact, something else is returned...

Note the second could be more efficiently written:

=IF(ISERROR(RC[-2]),0,IF(EXACT(RC[-6],[RC[-1]),1,...

 

[scotiajoe]

Thanks for the reply. Can you explain the meaning behind the secon
formula

 

[Dave Peterson]

Take a look at excel's help for =iserr() and =iserror().

Stolen from the help for =iserr(value)
Value refers to any error value except #N/A.

And for =iserror(value)
Value refers to any error value
(#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!).

So your formula looks like this:
=IF(ISERR(RC[-2]),0,IF(ISNA(RC[-2]),0,IF(EXACT(RC[-6],RC[-1]),1,

If it's any error except #n/a (iserr()), you return a 0. Then right after that,
you check to see if it's a #n/a (with the =isna() function. If it is #n/a, you
still return a 0.

So why not just use one error check that gets both: =iserror()?

 

[scotiajoe]

If I change the formula in the module to:

Range("G1").Select
ActiveCell.FormulaR1C1
"=IF(ISERROR(RC[-2]),0,IF(EXACT(RC[-6],[RC[-1]),1,0))"


I get a runtime error 100

 

[scotiajoe]

Nevermind, it works now. Thanks for all your help!