Hi,

I have been working on Excel VBA for some time now.. Actually what
i want to do is quite simple. i want to copy data from another sheet
in the same workbook checking the keyfield coloumn and i am trying to
get the values using the Application.WorksheetFunction.VLookup
like the syntax
given :"(Application.WorksheetFunction.VLookup(SearchStr ing, MyRange,
ReturnCol, False)") but the range is not coming through properly.When
i try to check the range in the immediate window it gives me a type
mismatch error... I am fetching the range as given below "(Set MyRange
= Sheets("SheetName (2)").Range("A7:B20")").... Can anyone please help
me on this it would be great if any one in the group can...

This works fine for me

SearchString = "abc"
ReturnCol = 2
Set MyRange = Sheets("SheetName (2)").Range("A7:B20")
x = Application.WorksheetFunction.VLookup(SearchString , MyRange, ReturnCol,
False)


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Raj" wrote in message
ups.com...
Hi,

I have been working on Excel VBA for some time now.. Actually what
i want to do is quite simple. i want to copy data from another sheet
in the same workbook checking the keyfield coloumn and i am trying to
get the values using the Application.WorksheetFunction.VLookup
like the syntax
given :"(Application.WorksheetFunction.VLookup(SearchStr ing, MyRange,
ReturnCol, False)") but the range is not coming through properly.When
i try to check the range in the immediate window it gives me a type
mismatch error... I am fetching the range as given below "(Set MyRange
= Sheets("SheetName (2)").Range("A7:B20")").... Can anyone please help
me on this it would be great if any one in the group can...

On Jul 3, 3:25 pm, "Bob Phillips" wrote:
This works fine for me

SearchString = "abc"
ReturnCol = 2
Set MyRange = Sheets("SheetName (2)").Range("A7:B20")
x = Application.WorksheetFunction.VLookup(SearchString , MyRange, ReturnCol,
False)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Raj" wrote in message

ups.com...



Hi,

I have been working on Excel VBA for some time now.. Actually what
i want to do is quite simple. i want to copy data from another sheet
in the same workbook checking the keyfield coloumn and i am trying to
get the values using the Application.WorksheetFunction.VLookup
like the syntax
given :"(Application.WorksheetFunction.VLookup(SearchStr ing, MyRange,
ReturnCol, False)") but the range is not coming through properly.When
i try to check the range in the immediate window it gives me a type
mismatch error... I am fetching the range as given below "(Set MyRange
= Sheets("SheetName (2)").Range("A7:B20")").... Can anyone please help
me on this it would be great if any one in the group can...- Hide quoted text -

- Show quoted text -

Is there any version problem.. I am using Office 2000 here....

You may want to post the code you used.

I like:

dim Res as variant
dim SomeValue as variant 'or string or long???
dim myRng as range
Set MyRange = Sheets("SheetName (2)").Range("A7:B20")
somevalue = "what goes here"
res = application.vlookup(somevalue,myrange,2, false)

if iserror(res) then
msgbox "an error was returned"
else
mesgbox res
end if

Notice that I didn't use application.worksheetfunction.vlookup.


Raj wrote:

On Jul 3, 3:25 pm, "Bob Phillips" wrote:
This works fine for me

SearchString = "abc"
ReturnCol = 2
Set MyRange = Sheets("SheetName (2)").Range("A7:B20")
x = Application.WorksheetFunction.VLookup(SearchString , MyRange, ReturnCol,
False)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Raj" wrote in message

ups.com...



Hi,

I have been working on Excel VBA for some time now.. Actually what
i want to do is quite simple. i want to copy data from another sheet
in the same workbook checking the keyfield coloumn and i am trying to
get the values using the Application.WorksheetFunction.VLookup
like the syntax
given :"(Application.WorksheetFunction.VLookup(SearchStr ing, MyRange,
ReturnCol, False)") but the range is not coming through properly.When
i try to check the range in the immediate window it gives me a type
mismatch error... I am fetching the range as given below "(Set MyRange
= Sheets("SheetName (2)").Range("A7:B20")").... Can anyone please help
me on this it would be great if any one in the group can...- Hide quoted text -

- Show quoted text -

Is there any version problem.. I am using Office 2000 here....

--

Dave Peterson

On Jul 3, 5:18 pm, Dave Peterson wrote:
You may want to post the code you used.

I like:

dim Res as variant
dim SomeValue as variant 'or string or long???
dim myRng as range
Set MyRange = Sheets("SheetName (2)").Range("A7:B20")
somevalue = "what goes here"
res = application.vlookup(somevalue,myrange,2, false)

if iserror(res) then
msgbox "an error was returned"
else
mesgbox res
end if

Notice that I didn't use application.worksheetfunction.vlookup.





Raj wrote:

On Jul 3, 3:25 pm, "Bob Phillips" wrote:
This works fine for me

SearchString = "abc"
ReturnCol = 2
Set MyRange = Sheets("SheetName (2)").Range("A7:B20")
x = Application.WorksheetFunction.VLookup(SearchString , MyRange, ReturnCol,
False)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Raj" wrote in message

oups.com...

Hi,

I have been working on Excel VBA for some time now.. Actually what
i want to do is quite simple. i want to copy data from another sheet
in the same workbook checking the keyfield coloumn and i am trying to
get the values using the Application.WorksheetFunction.VLookup
like the syntax
given :"(Application.WorksheetFunction.VLookup(SearchStr ing, MyRange,
ReturnCol, False)") but the range is not coming through properly.When
i try to check the range in the immediate window it gives me a type
mismatch error... I am fetching the range as given below "(Set MyRange
= Sheets("SheetName (2)").Range("A7:B20")").... Can anyone please help
me on this it would be great if any one in the group can...- Hide quoted text -

- Show quoted text -

Is there any version problem.. I am using Office 2000 here....

--

Dave Peterson- Hide quoted text -

- Show quoted text -

thanks for all of yor valued time in it.......... i found out a
another way which is working fine till now by putting the formula in
the cell and then getting the value for the same as given below.....

Range(pCol & iRow).Formula = _
"=VLOOKUP(" & pKeyCol & iRow & ",'" & sSchSheet & "'!"
& _
sRange & "," & SvlookupCol & ",0)"
Range(pCol & iRow).Value = Range(pCol & iRow).Text