Solved

VBA Macro Code for Excel 2010

Posted on 2012-03-18
4
329 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.

770 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