Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

VBA Macro Code for Excel 2010

Posted on 2012-03-18
4
Medium Priority
?
342 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

618 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