Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 346
  • Last Modified:

VBA Macro Code for Excel 2010

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
amkazen
Asked:
amkazen
  • 3
1 Solution
 
Chris BottomleyCommented:
You cannot mix addressing so use

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

Chris
0
 
Chris BottomleyCommented:
I think I missed a bracket!

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

Chris
0
 
Chris BottomleyCommented:
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
 
amkazenAuthor Commented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now