When using worksheet functions in VBA, don't forget the double quote
requirement. See your VBA help for Using Worksheet Functions.

"John Wong" wrote:

Hi all,

I have extracted the problem codes from my VB program in Excel. Actually, I
would like to set the formula in cell M1 in run time to

=MATCH("Soya Products",N14:N153,0) <----- Expected result

However, when I use the following code, the formula is set to
=MATCH("Soya Products",'N14':'N153',0) <----- Result generated by following
code

Can any one give me some advice? Thanks.

My Code is as follows:
---------------------------------------------------------------------------------------------
Dim myRange As String
Dim eRange As Range
Dim Lrow As Long

Lrow = Cells(Rows.Count, "N").End(xlUp).Row
myRange = "N14:N" & Lrow

Range(myRange).Select
Selection.Name = "eRange"
Set eRange = Range("eRange")
Range("M1").Select

ActiveCell.FormulaR1C1 = "=MATCH(""" & selectedCat(i) & """," &
eRange.Address(False, False) & ",0)"
ActiveWorkbook.Names("eRange").Delete