Solved

please solve a mystery

Posted on 2014-07-24
7
58 Views
Last Modified: 2014-07-30
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
Comment
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
  • Learn & ask questions
7 Comments
 
LVL 34

Accepted Solution

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

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

Open in new window


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

by:Norie
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

by:Jagwarman
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
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!

 
LVL 34

Expert Comment

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

Author Comment

by:Jagwarman
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 47

Expert Comment

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

Author Comment

by:Jagwarman
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

738 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