VBA Run Time Error 1004 - Range of object_Global failed - IF Formula

1
votes

I've inserted the below formula into VBA but am getting VBA Run Time Error 1004 - "Range of object_Global failed" - not sure what I'm missing.

Formula works in Excel but not when I translate to VBA. 

Range("AF") ="=IF(OR(AND(LEN('ACCOUNT DATA'!V2)=8,LEFT('ACCOUNT DATA'!V2,2)=""60""),LEFT('ACCOUNT DATA'!V2,3)=""CSN""),""CDR Created"",IF(OR(AND(LEN('ACCOUNT DATA'!X2)=8,LEFT('ACCOUNT DATA'!X2,2)=""60""),LEFT('ACCOUNT DATA'!X2,3)=""CSN""),""CDR Created"",IF(OR(AND(LEN('ACCOUNT DATA'!Y2)=8,LEFT('ACCOUNT DATA'!Y2,2)=""60""),LEFT('ACCOUNT DATA'!Y2,3)=""CSN""),""CDR Created"",""CDR Pending"")))"

CORRECTED FORMULA: I was missing the row number in my range... so AF2 instead of AF:

Range("AF2") ="=IF(OR(AND(LEN('ACCOUNT DATA'!V2)=8,LEFT('ACCOUNT DATA'!V2,2)=""60""),LEFT('ACCOUNT DATA'!V2,3)=""CSN""),""CDR Created"",IF(OR(AND(LEN('ACCOUNT DATA'!X2)=8,LEFT('ACCOUNT DATA'!X2,2)=""60""),LEFT('ACCOUNT DATA'!X2,3)=""CSN""),""CDR Created"",IF(OR(AND(LEN('ACCOUNT DATA'!Y2)=8,LEFT('ACCOUNT DATA'!Y2,2)=""60""),LEFT('ACCOUNT DATA'!Y2,3)=""CSN""),""CDR Created"",""CDR Pending"")))"

1
excelvbaruntime
The problem then is not in the formula itself, but in the VBA statement. Can you add the VBA statement? Something like myRange.Formula = "=IF(OR(AND(...)))" – kolcinx
@BranislavKollár I've updated the code – H.Martin

1 Answers

2
votes

That error usually means you're implicitly referring to the active sheet somewhere that you shouldn't be, for example:

Sheet2.Activate
Sheet1.Range(Cells(1,1), Cells(2,2)) = 42 'boom: "Cells" is referring to Sheet2

Here you're getting it because Range("AF") doesn't mean much to Excel. Try this instead:

[ActiveSheet.]Range("AF:AF").Formula = "..."

You'll probably want to calculate the actual rows though, because that will put the formula on every single row of column AF, which I doubt is what you really want.

Note, you should qualify that Range call with a proper Worksheet object, and since you're assigning the formula you should say so explicitly - a Range's default property points to its value, so you're leveraging quite a lot of implicit behavior for that code to work: best make as much as possible explicit.