Hi,

I have this piece of code which is giving me an run-time error "1004". I think i figured out the problem but wanted to ask


The vba code is as follows to insert a formula into a specific workbook-worksheet-cell locastion:
wrksht_BI_report.Range(alphacol(4 + x) & last_row1 + 1).Formula = _
    "=COUNTIFS([" & fl_new & "]" & ws_new & "!$" & alphacol(status_col_new) & ":$" & _
    alphacol(status_col_new) & "," & alphacol(4 + x) & "$1,[" & fl_new & "]" & ws_new & "!$" & _
    alphacol(Subject_New_col) & ":$" & alphacol(Subject_New_col) & ",$B" & last_row1 + 1 & ")"
in the example code above let x=1



Now the strange thing is that if the

fl_new = "Copy of empty_4_4_notemptyNewemptyOld_DET.xlsm" CODE FAILS


fl_new = "tst_4_4_Junk_DET.xlsm" CODE DOES NOT FAIL


So i have narrowed down the problem to the file-name. I manually opened the failing file and manually typed in this formula

COUNTIFS([Copy of empty_4_4_notemptyNewemptyOld_DET.xlsm]David!$I:$I,E$1,[Copy of empty_4_4_notemptyNewemptyOld_DET.xlsm]David!$A:$A,$B2)
and it took it.......again i manually did this not using vba.............



ANY IDEAS why using VBA in the manner i did would cause this problem?