0

I am writing a macro which inserts some formulas into a column array. I am trying to automate a weekly process, so the input files are variable and are selected through the msoFileDiaglogFilePicker application.

Dim wb_Final As Workbook, nameFinal As String
Set wb_Final = Workbooks.Open(Filename:=Final_Directory)
nameFinal = wb_Final.Name



Dim wb_Summary As Workbook, nameSummary As String
Set wb_Summary = Workbooks.Open(Filename:=Summary_Directory)
nameSummary = wb_Summary.Name

wb_Summary.Sheets("Sheet 1").Activate
With Sheets("Sheet 1")
.Range("AT4:AT5000").Formula = "=IF(OR(AX1=""Open"",AX1=""Won"",AX1=""Won - Pending""),""Yes"",""No"")"
.Range("AU4:AU5000").Formula = "=VLOOKUP(W:W,LOVs!H:I,2,FALSE)"
.Range("AV4:AV5000").Formula = "=IF(IFERROR(VLOOKUP($A:$A,'[" & nameFinal & "]DATA'!$A:$AK,34,FALSE),0)=0,"",VLOOKUP($A:$A,'[" & nameFinal & "]DATA'!$A:$AK,34,FALSE))"
.Range("AW4:AW5000").Formula = "=IF(IFERROR(VLOOKUP($AV:$AV,'[" & nameFinal & "]DATA'!$AH:$CX,48,FALSE),0)=0,"",VLOOKUP($AV:$AV,'[" & nameFinal & "]DATA'!$AH:$CX,48,FALSE))"
'....More formulas similar to above
End With

The first two formulas get placed into the cells and compute with no problem.

The third formula gets placed into the cells as a text with an apostrophe in the front of it in excel. (i.e. '=IF(IFERROR(VLOOKUP...)

The fourth formula generates a Run-time error '1004'

I have tried all the different formula types:

.Formula .FormulaR1C1 .FormulaLocal .FormulaR1C1Local

And still receive the same error.

I think I am experiencing a similar issue as stated in this article, but I'm not if I can use the Application.Vlookup function without redefining all the arrays and column references in my current VLOOKUP functions (which would take a VERY long time).

Any help would be much appreciated

Jarom
  • 1,067
  • 1
  • 14
  • 36
iMILES
  • 11
  • 1
  • Sounds like it could be a formatting issue to me. I would delete the column and insert it again to reset the formatting. If I'm wrong you only wasted a couple of seconds, if I'm right, easy fix! Though I do see that you are using an apostrophe in the formula as @Napoli noted. That will for sure cause a problem. – Jarom May 04 '18 at 21:22
  • I just tried this. Still experience the same error :( When I leave the formula as is with the apostrophe, the formula shows up as a text. When I go into the cell and remove the apostrophe from the front of the formula, it asks if I want to correct the formula. When the excel corrects the formula, it performs the VLOOKUP. The problem is - I cant figure out what the difference is from the formula I inputted (minus the apostrophe in front) and the corrected formula. – iMILES May 04 '18 at 22:01
  • Consider `Debug.Print`ing the fourth formula, copy the output from the immediate window, paste formula into any cell and see if Excel displays the same error (if it does, see if a particular character range is highlighted in the formula bar). – chillin May 04 '18 at 23:23

2 Answers2

0

You cannot use apostrophes in formulas; instead use quotation marks, and in your case, you'll need to escape it like you did in your prior formulas, example:

VLOOKUP($A:$A,""[" & nameFinal & "]DATA""!$A:$AK,34,FALSE)
Napoli
  • 1,389
  • 2
  • 15
  • 26
0

Try

With Worksheets("Sheet 1") '>== or Sheet1 depending on actual name
    .Range("AW4:AW5000").Formula = "=IF(IFERROR(VLOOKUP($AV:$AV,[" & nameFinal & "]DATA!$AH:$CX,48,FALSE),0)=0,"""",VLOOKUP($AV:$AV,[" & nameFinal & "]DATA!$AH:$CX,48,FALSE))"
End With
QHarr
  • 83,427
  • 12
  • 54
  • 101
  • No, unfortunately not :( – iMILES May 07 '18 at 16:27
  • What happens? Is there an error message? Does it still error on this line? – QHarr May 07 '18 at 16:28
  • After making the changes you suggested, I Still experiencing the same error as above: third formula populates into the array as a text (with an apostrophe in front) and the fourth formula produces a Run-time error '1004' – iMILES May 07 '18 at 16:45