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

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

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

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.

--
Don Guillett
SalesAid Software

"James Cornthwaite" wrote in message
...
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

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

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


"Don Guillett" wrote in message
...
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.

--
Don Guillett
SalesAid Software

"James Cornthwaite" wrote in message
...
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

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

Try

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


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



"James Cornthwaite" wrote in
message ...
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

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

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


"Chip Pearson" wrote in message
...
Try

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


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



"James Cornthwaite" wrote in message
...
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

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

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




"James Cornthwaite" wrote in
message ...
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


"Chip Pearson" wrote in message
...
Try

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


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



"James Cornthwaite" wrote in
message ...
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

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

it does now,

great

THANKS


"Chip Pearson" wrote in message
...
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




"James Cornthwaite" wrote in message
...
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


"Chip Pearson" wrote in message
...
Try

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


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



"James Cornthwaite" wrote in message
...
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