Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

Posted on 2014-07-24
Medium Priority
61 Views
I am entering the following formula through a Macro in Excel 2010,

Range("T2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC[-7],Reviewit,1,0)),""Review"","""")"

When my macro completes in Cell T2 I see not the result of the formula but

=IF(ISERROR(VLOOKUP(RC[-7],Reviewit,1,0)),"Review","")

Can an expert please tell me how to resolve this.
0
Question by:Jagwarman
[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

LVL 35

Accepted Solution

ste5an earned 1500 total points
ID: 40216454
Using the more compact form

``````ActiveSheet.Range("T2").FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-7],Reviewit,1,0)),""Review"","""")"
``````

works for me. Sure that you have pasted the exact code snippet? Check the number of quotes. Test it also on a new sheet/workbook.
0

LVL 34

Expert Comment

ID: 40216502
Do you mean you see the actuall string '=IF(ISERROR(VLOOKUP(RC[-7],Reviewit,1,0)),"Review","")'?

If you do then it could be that you Excel set to show formulas (Formulas tab>Show Formulas) or it could be that the T2 is formatted as Text.
0

Author Comment

ID: 40216803
ste5an tried all that but still not working. All I can see is the code.

imnorie checked that and it's not formatted as text
0

LVL 34

Expert Comment

ID: 40216866
Did you check to see if Excel is set to show formulas?
0

Author Comment

ID: 40216932
yes done that

I am working on two tabs one tab works fine the other does not. I have even started a new file and copied the Macro into that and same thing happens, one tab fine one not

Bizzare
0

LVL 49

Expert Comment

ID: 40217019
Can you attach a workbook that shows the problem?
0

Author Comment

ID: 40217117
Problem solved.

I am copying form another file and it is that file that has some weird thing about it so when I copy from A to R my Calc stops working

if I copy from A to Q it works fine

I will get my question removed

Regards
0

## Featured Post

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.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calculâ€¦
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
###### Suggested Courses
Course of the Month8 days, 22 hours left to enroll