Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 121
  • Last Modified:

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

0
Seamus2626
Asked:
Seamus2626
  • 4
  • 3
1 Solution
 
aikimarkCommented:
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

0
 
Seamus2626Author Commented:
Hey man, that returned a value error
0
 
aikimarkCommented:
@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

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Seamus2626Author Commented:
@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
0
 
aikimarkCommented:
Unfortunately, I don't have Sharepoint in my environment to try and recreate your problem. :-(
0
 
Seamus2626Author Commented:
All good aikimark, sorted now, thanks!
0
 
aikimarkCommented:
@Seamus

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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now