using excel functions in a macro - not familiar with macros at all

  • Thread starter Thread starter James Cornthwaite
  • Start date Start date
J

James Cornthwaite

I have written my own function FindNominal (as below) in a macro in excel. I
want to use this function repeatedly in my excel spreadsheet.


Function FindNominal(NomCode)

FindNominal = Vlookup(NomCode, IMPORTDOC, 5 false)
End Function


but it does NOT work.


It reports
Vlookup is not recognised.
Do i need to write an import statement?

IMPORTDOC is defined as a range of cells in excel by the name, define
command.
I take it this is acceptable?

Thanks
 
look in vba help for using functions in macros. An easy way to do this is
from the vbe just type in vlookup and touch the f1 key.
 
sorry bout that.

I now know i have to use ""Worksheetfunction"".VLookup(...................

to get vlookup recognised.

However VB doesn't recognise my constant defined in my workbook.

I defined it by insert | name | define

Any ideas how to get IMPORTDOC recognised in VB so i can use it as an arg in
my vlookup call

Thanks
 
Try

FindNominal = Vlookup(NomCode, Range("IMPORTDOC"), 5 false)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Hi
couldn't get range to work.

Cant get any info on the range function from help in vb
Should importdoc be the name of one of my excel worksheets or (as it is
currently) a range of cells currently defined in excel.

In summary I have so far

Function FindNominal(NomCode)
FindNominal = WorkSheetfunction.VLookup(NomCode, ???????, 5, false)
End Function

I have nothing else within my macro
I haven't created any objects or anything, as i say i have no experience
with macros

what do i need to put in where the ????? marks are
Currently I have an excel worksheet called 'import' with a defined range of
cells IMPORTDOC


Please please help, i just dont know

Thanks
James
 
James,

FindNominal = WorksheetFunction.VLookup(NomCode,
Range("IMPORTDOC"), 5, False)

will work as long as IMPORTDOC is at least 5 columns wide.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
it does now,

great

THANKS


Chip Pearson said:
James,

FindNominal = WorksheetFunction.VLookup(NomCode, Range("IMPORTDOC"), 5,
False)

will work as long as IMPORTDOC is at least 5 columns wide.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

using excel functions in a macro - my first macro! ? 1
My first macro - doesnt work - function not recognised 2
My first macro, uses Vlookup but doesnt update unless hit I enter 3
Macro Query - 6
Color contents of a cell accessed by a function? 4
changing the appearance of a cell accessed 1
Add comments via a function - please could you ammend my code/ pseudocode to work 6
Accessing a variable(a range) and modifying it 1

Back
Top