VBA Code for Index Match to return values only - Not Formula

[hrayani]

Hello Friends,

How can this formula be written in a VBA code to return results only - not formula

=INDEX(suppliers,MATCH(B11,ref,0))

suppliers & ref are named ranges within the worksheet

I want the it to be applied to C11:C77

Any help would be appreciated.

Regards,

Humayun

 

[Fluff]

How about

With Range("C11:C77")
   .Formula = "=INDEX(suppliers,MATCH(B11,ref,0))"
   .Value = .Value
End With

 

[hrayani]

Thanks fluff,

Your solution is working just perfect

Is the below way of adding another range is correct - it is working fine though

Sub hello()

With Range("E1:E5")
   .Formula = "=INDEX(suppliers,MATCH(D1,ref,0))"
    .Value = .Value
   End With
   
  With Range("F1:F5")
  .Formula = "=INDEX(customers,MATCH(D1,ref,0))"
   .Value = .Value
End With

End Sub

or can it be shorten ??

Regards,

Humayun

 

[Fluff]

That's fine.

 

[hrayani]

That's fine.

Thanks

 

[Fluff]

You're welcome & thanks for the feedback.