from access VBA to excel sheet

[aajaydee]

I trying to run code

IF(ISNA(VLOOKUP(h2,i7:i9,1,FALSE)),"No","Yes")

and/or

Application.WorksheetFunction.VLookup(Look_Value, Range(h7:i9), cwfR + 1, False)

From MS access (vba) and getting error

any suggestion how to make it run

thanks

 

[SkipVought]

[tt]
Application.WorksheetFunction.VLookup(Look_Value, Worksheets("YourSheetName").Range("h7:i9"), cwfR + 1, False)
[/tt]


Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[aajaydee]

I appreciate your prompt response

here are the full code
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Set objXL = New Excel.Application
objXL.Visible = True
Set objWkb = objXL.Workbooks.Open("c:\excel_templates\template_A1.xls")
dim fvariabe

Set objSht = objWkb.Worksheets("output")
I figure out that

fvariabe = objSht.Application.WorksheetFunction.VLookup(Look_Value, Range("H7:i9"), 2, False)

it's working
I have question

Is it there a way I can limit third arg which I'm using 2 mean I have to look in entire 2 colum
it there a way I can limit this to look up to certain row in that colum.

thanks

 

[SkipVought]

Your lookup range is H7:I9 -- THREE ROWS!

You're looking up the value in Look_Value in H7:H9 and returning the corresponding value in column I (second column in the lookup range)

So what's the problem????

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[aajaydee]

Thanks for kind reply
it's working now
I just have little trouble in syntex
I need to work with dynamic range value
like range("f" & mr : g & cr")
where mr is integer giving me row number
and cr is integer giving me cloumn number
it will help me eo change search limit dynamically

please help me out in syntax

thanks

 

[PHV]

Range("F" & mr & ":G" & cr)


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886