Solved

VBA Macro Code for Excel 2010

Posted on 2012-03-18
4
328 Views
Last Modified: 2012-03-23
Hi,
Can you help debug the below macro code? Thanks.

  Range("Q2").Select
     Application.CutCopyMode = False
     ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[-1]),(VLOOKUP($O2,Sheet2!$A$1:$E$4,2,FALSE),RC[-1])"

I am getting a runtime error 1004  application-defined or object-defined error

I have 4 columns: P, Q, R, & S that need to be populated if they are blank based on the values in a table on a separate sheet, sheet 2 if those values match the data in the cells in column O.

Cells in columns P & Q may be blank, or they may be populated already.  If populated, I need to keep that data.

Additionally, this data is updated daily real-time so I need to extract the data from the financial system  every 2 - 3 days.  After extracting it, I verify the data.  However, once verified it is verified.  I just need to find the new updated entries, and verify them.  So, column R is the verified column and is populated with yes or no.  There is a unique ID # in column D so how can I match the new extracted data with the previous day's verified data, and trabsfer the Yes verified to the new extract data in column R?

I have the following code that works great and is similar to what is needed above but it does not involve a vlookup & isblank combination.  And, can I make this it's own separate sub (that I can do easily), without any care as to what cell is active prior to calling the sub, or do I need to always get to the proper column (& 1st cell) prior to calling the sub?
0
Comment
Question by:amkazen
  • 3
4 Comments
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 37736538
You cannot mix addressing so use

ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[-1]),(VLOOKUP(r2c15,Sheet2!r1c1:r4c5,2,FALSE),RC[-1])"

Chris
0
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 500 total points
ID: 37736539
I think I missed a bracket!

ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[-1]),(VLOOKUP(r2c15,Sheet2!r1c1:r4c5,2,FALSE)),RC[-1])"

Chris
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 37739051
For what it's worth ... and I do not recommend this because of the complexity but you can convert on the fly using application.ConvertFormula:

ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[-1]),(VLOOKUP(" & application.ConvertFormula("$O2", xlA1, xlR1C1, true) & ",Sheet2!" & application.ConvertFormula("$A$1:$E$4", xlA1, xlR1C1, true) & ",2,FALSE)),RC[-1])"

Chris
0
 

Author Closing Comment

by:amkazen
ID: 37759426
Thanks, Chris.  This worked.  I did not try to use the more complex answer but thanks for providing it in case I ever need it.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

920 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

16 Experts available now in Live!

Get 1:1 Help Now