Index with Match By Code

[LearnMeExcel]

HI
i am trying to learn VBA
and i try this code

Sub IndexwithMatch()
Dim LR As Integer
LR = Range("a" & Rows.Count).End(xlUp).Row
WorksheetFunction.Index(range("a4:a" & LR),WorksheetFunction.Match(Range("a1"),range("b4:b" & LR),false))
End Sub

with this data

Excel Workbook
	A	B
1	Local
2
3	Names
4	Name1	not
5	Name2	Yet
6	Name3	Do
7	Name4	Local
8	Name5	ok
Sheet1

Excel 2007

i know we can do it by Formula

=INDEX(A4:A8,MATCH(A1,B4:B8,0))

but
what is the wronge with my code

 

[meldoc]

Hello

Try.

Sub IndexwithMatch()
Dim LR As Integer
LR = Range("a" & Rows.Count).End(xlUp).Row
result = WorksheetFunction.Index(Range("a4:a" & LR), WorksheetFunction.Match(Range("a1"), Range("b4:b" & LR), False))
End Sub

 

[dscg]

You have the correct formula, it's just expecting to return a string from this function, which is not set up, so the compiler doesn't know what you want it to do. Consider:

Sub IndexwithMatch()
Dim LR As Integer
Dim myStr As String

LR = Range("a" & Rows.Count).End(xlUp).Row

myStr = WorksheetFunction.Index(Range("a4:a" & LR), WorksheetFunction.Match(Range("a1"), Range("b4:b" & LR), False))

MsgBox (myStr)

End Sub

 

[LearnMeExcel]

Really many thanx
but how i can get the Result in Any Cell

 

[meldoc]

Try.

Sub IndexwithMatch()
Dim LR As Integer
LR = Range("a" & Rows.Count).End(xlUp).Row
result = WorksheetFunction.Index(Range("a4:a" & LR), WorksheetFunction.Match(Range("a1"), Range("b4:b" & LR), False))
Cells(1, 3) = result
Range("C2") = result
End Sub

 

[LearnMeExcel]

Thanx Meldoc
it is working now
that is mean i have to make String name to haold the result when i am working with worksheeetfunction in VBA

 

[meldoc]

There are two trains of thought on that, to declare your variables or not.
Dscg does, I tend not to you decide.
BTW. this may be of some use to you.

http://www.cpearson.com/excel/DeclaringVariables.aspx
http://msdn.microsoft.com/en-us/library/aa139976(office.10).aspx

Have fun.

Edit, Added link.