asked on

Calling Formula

Hi,

Im using a macro button to call the below formula

When i press the button it returns a ref error, if i click it again, it returns the correct formula, how is this possible?

If i step through on the editor, it temporarily returns ref but nealry instantly reurns formula, im confused!

Sub Check_Date()


Range("C13").Formula = "=IF(OR(INT('http://teams.global.hsbc/gtb/Global-SPM/client-comms/PB-PM/Shared Documents/Expiring Content - Maintenance Reports/[NA.xlsm]PB Review'!$H$1)=B13,INT('http://teams.global.hsbc/gtb/Global-SPM/client-comms/PB-PM/Shared Documents/Expiring Content - Maintenance Reports/[Europe.xlsm]PB Review'!$H$1)=B13,INT('http://teams.global.hsbc/gtb/Global-SPM/client-comms/PB-PM/Shared Documents/Expiring Content - Maintenance Reports/[MENA.xlsm]PB Review'!$H$1)=B13,INT('http://teams.global.hsbc/gtb/Global-SPM/client-comms/PB-PM/Shared Documents/Expiring Content - Maintenance Reports/[Asia.xlsm]PB Review'!$H$1)=B13,INT('http://teams.global.hsbc/gtb/Global-SPM/client-comms/PB-PM/Shared Documents/Expiring Content - Maintenance Reports/[LATAM.xlsm]PB Review'!$H$1)=B13),""Yes"",""No"")"

    Range("C13").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

End Sub

Open in new window

aikimark

Please try this condensed version.

Sub Check_Date()


    Range("C13").Value = Application.Evaluate("=IF(OR(INT('http://teams.global.hsbc/gtb/Global-SPM/client-comms/PB-PM/Shared Documents/Expiring Content - Maintenance Reports/[NA.xlsm]PB Review'!$H$1)=B13,INT('http://teams.global.hsbc/gtb/Global-SPM/client-comms/PB-PM/Shared Documents/Expiring Content - Maintenance Reports/[Europe.xlsm]PB Review'!$H$1)=B13,INT('http://teams.global.hsbc/gtb/Global-SPM/client-comms/PB-PM/Shared Documents/Expiring Content - Maintenance Reports/[MENA.xlsm]PB Review'!$H$1)=B13,INT('http://teams.global.hsbc/gtb/Global-SPM/client-comms/PB-PM/Shared Documents/Expiring Content - Maintenance Reports/[Asia.xlsm]PB Review'!$H$1)=B13,INT('http://teams.global.hsbc/gtb/Global-SPM/client-comms/PB-PM/Shared Documents/Expiring Content - Maintenance Reports/[LATAM.xlsm]PB Review'!$H$1)=B13),""Yes"",""No"")")

End Sub

Open in new window

Seamus2626

ASKER

Hey man, that returned a value error

aikimark

@Seamus

I have no way to evaluate the formula, since it references a non-accessible URL location. What can
you tell me about it? Most of the time, I would have expected UNC file references like this:

Sub Check_Date()

    Range("C13").Value = Application.Evaluate("=IF(OR(INT('\\teams.global.hsbc/gtb/Global-SPM/client-comms/PB-PM/Shared Documents/Expiring Content - Maintenance Reports/[NA.xlsm]PB Review'!$H$1)=B13,INT('\\teams.global.hsbc/gtb/Global-SPM/client-comms/PB-PM/Shared Documents/Expiring Content - Maintenance Reports/[Europe.xlsm]PB Review'!$H$1)=B13,INT('\\teams.global.hsbc/gtb/Global-SPM/client-comms/PB-PM/Shared Documents/Expiring Content - Maintenance Reports/[MENA.xlsm]PB Review'!$H$1)=B13,INT('\\teams.global.hsbc/gtb/Global-SPM/client-comms/PB-PM/Shared Documents/Expiring Content - Maintenance Reports/[Asia.xlsm]PB Review'!$H$1)=B13,INT('\\teams.global.hsbc/gtb/Global-SPM/client-comms/PB-PM/Shared Documents/Expiring Content - Maintenance Reports/[LATAM.xlsm]PB Review'!$H$1)=B13),""Yes"",""No"")")

End Sub

Open in new window

Seamus2626

ASKER

@aikimark

The formula is referencing sharepoint sites - i am very confused to why it returns the correct result when i run the sub twice (i.e. ckick the button twice)

So there is a cell on the current worksheet with todays date - the formula checks the date stamp on the files in sharepoint to see if they match

Thanks

ASKER CERTIFIED SOLUTION

aikimark

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Seamus2626

ASKER

All good aikimark, sorted now, thanks!

aikimark

@Seamus

What was the solution? My comment does not appear to be a fix for the problem.