Back to Forms in Excel VBA archive index

Back to archive home

Hi

First post -I'm a novice - but I've learned all I know from these pages!

First post -I'm a novice - but I've learned all I know from these pages!

This is the formula :(say it's in cell R48)

=IF(ISERROR(VLOOKUP(R46,$A$3:$C$550,2,FALSE)),"error muster nmbr missing ",VLOOKUP(R46,$A$3:$C$550,2,FALSE))

(the formula looks up names in col 2)

When I set up the s/sheet originally, if the 'R46'

ref (in col 2) was blank, the cell R48 reflected the blank.

If there was a name in the cell, it reflected the name.

However, if I now delete the name (using the delete key)cell

R48 shows a 0 (zero)instead of a blank cell.

Could someone help me out here? (be gentle with me!)

Thanks in advance

Check out our Excel Resources | ||||

Hi Sandy,

I don't know for sure, but there is some peculiar issue involved with the pair 'blank' and 'empty'. The cell was first BLANK; after the delete operation, it became EMPTY. This subtle difference (hard to say why this should be case) shows up thru some formulas like yours.

I'd like take the opportunity and make some suggestions to modify your formula.

=IF(ISBLANK(R46),"",IF(ISNUMBER(MATCH(R46,$A$3:$A$550,FALSE)),"Error: Master number missing",VLOOKUP(R46,$A$3:$C$550,2,FALSE)))

The first IF should take care of the BLANK/EMPTY issue. The second IF is equivalent to your VLOOKUP formula, but can be a bit less costly with large sets of data.

Additionally, you may insert 0 for FALSE to make the formula a bit shorter because FALSE=0 and TRUE=1.

To make the formula a bit nicer (IMHO), you could select the range $A$3:$A$550 and give it a name, say LVALUES (for lookup values), via the Name Box or the option Insert|Name|Define. Accordingly, you could name, say DATA, the range $A$3:$C$550.

The formula, rewritten, would look as:

=IF(ISBLANK(R46),"",IF(ISNUMBER(MATCH(R46,LVALUES,0)),"Error: Master number missing",VLOOKUP(R46,DATA,2,0)))

Hope this helps.

Aladin

=============

THANKS Aladin, for taking the time to give me such

invaluable help ! It worked a treat - and I've learned a lot from your reply !

This archive is from the original message board at www.MrExcel.com.

All contents © 1998-2004 MrExcel.com.

Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.

Microsoft Excel is a registered trademark of the Microsoft Corporation.

MrExcel is a registered trademark of Tickling Keys, Inc.

All contents © 1998-2004 MrExcel.com.

Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.

Microsoft Excel is a registered trademark of the Microsoft Corporation.

MrExcel is a registered trademark of Tickling Keys, Inc.