Error in setting formula (Match) with dynamic variables
 

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

Error in setting formula (Match) with dynamic variables
 

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

Error in setting formula (Match) with dynamic variables
 

instead of
ActiveCell.FormulaR1C1
use
ActiveCell.Formula

--
Regards,
Tom Ogilvy


"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