Error in setting formula (Match) with dynamic variables

J

John Wong

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
 
J

JLGWhiz

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top