?
Solved

VBA Macro Code for Excel 2010

Posted on 2012-03-18
4
Medium Priority
?
335 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
[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
  • 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 2000 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

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

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

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

752 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