Convert Excel formula to VBA code

[SalimJ]

Hi

Please can you help me convert the following formula to vba code. Moreover, I need to access the Employee table that is found in another workbook.
[Excel formula]
=INDEX(Employee,MATCH("ZJ",Employee[ID],0),COLUMN(Employee[Name]))
[/Excel formula]

 

[steve the fish]

Something like this?

Dim ws As Worksheet, myRow As Long
Dim rngID As Range, rngName As Range

Set ws = Workbooks("Book2").Sheets("Sheet1")
Set rngID = ws.Range("Employee[ID]")
Set rngName = ws.Range("Employee[Name]")

myRow = Application.Match("ZJ", rngID, 0)
If Not IsError(myRow) Then MsgBox Application.Index(rngName, myRow)

It will need adjusting to suit your workbook. That will provide the answer in a message box.

 

[SalimJ]

Hi Steve the fish,

Thank you very much for the valuable code. However, how do I handle when there is no matching? Actually, I'm getting "Type mismatch".

Please help.

Sorry I'm not used to VBA programming.

Thanks

 

[steve the fish]

Sorry yes change the type to variant instead of long for myRow.

 

[SalimJ]

Hi,

It works great now. Thank you very much.

 

[SalimJ]

Hi,

Can anyone please tell me what is wrong with the following. I'm getting "Type Mismatch" on myRow.

Dim ws As Worksheet, myRow As Variant
Dim rngItem As Range, rngSize As Range
Dim rngRate As Range


Set ws = Workbooks("Book2").Sheets("Sheet1")
Set rngItem = ws.Range("Table[Item]")
Set rngSize = ws.Range("Table[Size]")
Set rngRate = ws.Range("Table[Rate]")


myRow = Application.Match("Jacket" & "Large", rngItem & rngSize, 0)
'myRow = Application.Match(1, ("Jacket" = rngItem) * ("Large" = rngSize), 0)
If Not IsError(myRow) Then MsgBox Application.Index(rngRate, myRow)

Thanks

 

[steve the fish]

Try it like this and see if its ok:

myRow = Evaluate("MATCH(1,(" & rngItem.Address(External:=True) & "=""Jacket"")*(" & rngSize.Address(External:=True) & "=""Large""),0)")

 

[SalimJ]

Hi,

It works perfect. Thank you.

Is there a way to return more than one column as result; for example if I want to return [Rate] and [Location]

Thanks

 

[SalimJ]

Hi steve the fish,

I got what I wanted regarding the multiple result.

Thank you very much for your help.