0

I am working on a excel reporting tool. The scenario is like this, when I click the button on my excel reporting tool I want to paste my "iferror(vlookup)" formula on the selected active workbook and that formula will do the lookup on another different workbook.

Here is my current code:

Workbooks("OR_" & fMonth & "selected active workbook.xlsx").Activate

f1 = "=COUNTIF(workbook where to lookup" & month & ".xlsx!$C:$C,J2)"            
f2 = "=IFERROR(VLOOKUP(J2,workbook where to lookup" & month & ".xlsx!$C:$E,3,FALSE),"")"  

ActiveSheet.Range("BK2").formula = f1         
ActiveSheet.Range("BL2").formula = f2

But when I run this code I am getting this error: run-time error '1004':
Application-defined or object-defined error.

Please help me get this done. Any response will be appreciated. Thanks in advance

Community
  • 1
  • 1
JCBA
  • 53
  • 1
  • 11
  • by the way I am using Microsoft2013 – JCBA Oct 19 '17 at 08:36
  • Which line? Last time I checked COUNTIF had two arguments. – SJR Oct 19 '17 at 08:43
  • on the f2 = "=IFERROR(VLOOKUP(J2,workbook where to lookup" & month & ".xlsx!$C:$E,3,FALSE),"")" – JCBA Oct 19 '17 at 08:46
  • f1 is working just fine – JCBA Oct 19 '17 at 08:46
  • when I'm trying to run the code while commenting out the line ActiveSheet.Range("BL2").formula = f2) it does not give me any error but when that line is active the error pops out – JCBA Oct 19 '17 at 08:49
  • Really, I'm amazed. Is the )" at the end of that line a typo? – SJR Oct 19 '17 at 08:53
  • yes just a typo – JCBA Oct 19 '17 at 08:54
  • You need to double up the quotes inside f2 at the end. – SJR Oct 19 '17 at 08:55
  • do you mean like this f2 = "=IFERROR(VLOOKUP(J2,LSR_WISOR_USERS_" & month & ".xlsx!$C:$E,3,FALSE),"")""" – JCBA Oct 19 '17 at 08:56
  • `"=IFERROR(VLOOKUP(J2,workbook where to lookup" & month & ".xlsx!$C:$E,3,FALSE),"""")" ` But there is no way that f1 can work as you have posted it. If it's another typo please correct it. – SJR Oct 19 '17 at 08:57
  • 1
    I am getting no error now after I added another double quote just like what you said, I am very thankful that you help me out. For the f1 I am getting no error for that, the formula is being placed on my selected ranges. Could you tell me what is the problem with f1? as I'm really not getting any error for that line. – JCBA Oct 19 '17 at 09:11
  • Look up COUNTIF in Excel help or Google it. You are using `=COUNTIF(J2,somerange,J2)` which is not right. – SJR Oct 19 '17 at 09:13
  • I see, by reviewing my original code it shows that it is another typo, may I know how is that adding another "" fixed the error? I desperately looking for a fix almost the whole day last day trying to fix that error. Good thing I created an account here at stack overflow and be able to help by you. – JCBA Oct 19 '17 at 09:20
  • Tbh I don't know why, I just know that you have to double them up. I guess it's do with Excel otherwise thinking that you are closing the previous quotes. There is discussion of different methods here https://stackoverflow.com/questions/9024724/how-do-i-put-double-quotes-in-a-string-in-vba – SJR Oct 19 '17 at 09:23
  • 1
    I see, thank you very much SJR for your big help. I really appreciated it. =) – JCBA Oct 19 '17 at 09:25

0 Answers0