Solved

please solve a mystery

Posted on 2014-07-24
7
53 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
7 Comments
 
LVL 32

Accepted Solution

by:
Stefan Hoffmann earned 500 total points
Comment Utility
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 33

Expert Comment

by:Norie
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 33

Expert Comment

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

Author Comment

by:Jagwarman
Comment Utility
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 45

Expert Comment

by:Martin Liss
Comment Utility
Can you attach a workbook that shows the problem?
0
 

Author Comment

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now