Solved

please solve a mystery

Posted on 2014-07-24
7
60 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 48

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
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 will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

617 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