[mso] Re: Another Excel VBA Question

Hi Christine,

A couple of thoughts on your formula. There's a function ISNA which
specifically tests for #N/A, which could reduce the number of IF
functions. You also have a space in between the double quotes for FALSE
part of the formula. I suggest that would be better if you eliminated the
space between the double quotes just so the cell is actually empty, rather
than have a blank character in there and visually it appears empty. It may
be nit-picking but the function ISBLANK will return false in your
condition, even though it appears to be true.

Wrt your main problem, you can put this into a macro. E.g. In the VBA
editor, Insert a module if you haven't already got one. Paste the
following code into the module (watch out for it being line wrapped in the
email):

Sub FillWithFormula()
  Dim strLookupCell As String
  Dim strLookupRange As String
  strLookupCell = InputBox("Enter lookup value cell (including $ if
needed)")
  strLookupRange = InputBox("Now enter lookup range of values")
  ActiveCell.Formula = "=IF(ISERROR(ERROR.TYPE(VLOOKUP(" _
    & strLookupCell & "," & strLookupRange & ",4,FALSE))),VLOOKUP(" _
    & strLookupCell & "," & strLookupRange & ",4,FALSE),
IF(ERROR.TYPE(VLOOKUP(" _
    & strLookupCell & "," & strLookupRange & ",4,FALSE))=7, 0, "" ""))"
End Sub

Select the cell where you want the formula to go, and run the macro,
either from the Tools-Macro-Macros-{select macro FillWithFormula}-Run or
by assigning a short-cut key. You'll be prompted for the lookup cell, the
lookup range, then the formula will be inserted into the selected cell.

Also, if you are not familiar with VB syntax, the underscore "_" separated
from the line by a space, indicates the statement is continued over on the
next line.

Note the use of doubled double-quotes as they are nested, i.e. inside a
pair of double-quotes, if you need double-quotes you have to put them in
twice.

Hope this all makes sense.

Best regards, John
-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx] On Behalf
Of McDonald, Christine, Ms, DCAA
Sent: 29 April 2004 01:31
To: mso@xxxxxxxxxxxxx
Subject: [mso] Another Excel VBA Question


I have the following formula, which eliminates the #N/A from the results
of a vertical lookup when there is no match.  Is it possible to create a
macro that create the formula and prompts for the vlookup values so I
don't have to type the entire formula each time I need to use it?  Or
better yet, since the vlookup is in the formula 3 times, rather than
prompting 3 times for the information,  prompting once, storing it as a
variable and then putting in the correct place in the vlookup?
=IF(ISERROR(ERROR.TYPE(VLOOKUP($A3,data1999,4,FALSE))),
VLOOKUP($A3,data1999,4,FALSE),
IF(ERROR.TYPE(VLOOKUP($A3,data1999,4,FALSE))=7, 0, " "))

Thanks


Christine McDonald
Technical Specialist
Western Regional Office


*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, send an email to
mso-request@xxxxxxxxxxxxx with the word "unsubscribe" (without the quotes)
in the subject line.

Or, visit the group's homepage and use the dropdown menu.  This will also
allow you to change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to use the files section for sharing files with the group, send
a request to mso-moderators@xxxxxxxxxxxxx and you will be sent an
invitation with instructions.  Once you are a member of the files group,
you can go here to upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************


*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or 
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, send an email to 
mso-request@xxxxxxxxxxxxx with the word "unsubscribe" (without the quotes) in 
the subject line.

Or, visit the group's homepage and use the dropdown menu.  This will also allow 
you to change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to use the files section for sharing files with the group, send a 
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with 
instructions.  Once you are a member of the files group, you can go here to 
upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************

Other related posts: