Solved

excel vba how to add formula

Posted on 2016-07-21
5
54 Views
Last Modified: 2016-07-27
I'm getting error when I assign below formula to field A21

Run time error 1004
Application defined or object defined error
 
    Range("A21").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(A2,Sheet2!$A$10:$R$90,3,FALSE)"
    Range("A21").Select

If I apply same formula manually on cell A21 by pressing F2 the commend works.
0
Comment
Question by:Bharat Guru
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 34

Accepted Solution

by:
Norie earned 500 total points
ID: 41723802
Your formula uses A1 notation, not R1C1, try this.
    Range("A21").Formula = "=VLOOKUP(A2,Sheet2!$A$10:$R$90,3,FALSE)"

Open in new window

0
 
LVL 3

Expert Comment

by:AL_XResearch
ID: 41723804
Yes of course. You are assigning an A1 style formula to the R1C1 formula property.

If you want to assign that formula you should just use the 'mycell.formula' property
0
 
LVL 3

Expert Comment

by:AL_XResearch
ID: 41723807
Looks like a split-second clash of experts :)
0
 

Author Comment

by:Bharat Guru
ID: 41730167
Thanks
0
 

Author Closing Comment

by:Bharat Guru
ID: 41731660
Thanks
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question