VBA and formula help for simple code/formula

[ItalianPlatinum]

Hello - i have two issues that i am trying to solve for and looking for a little bit of guidance.

1. A formula to pull Prior business day. I thought the formula I have would work but when I add a holiday on my list of say 12/27/22 (TEST) it should pull 12/23/22. seeing today is 12/28/22, yesterday is a holiday and day prior to yesterday is 12/26/22 (holiday). then weekend of 12/24 - 12/25. So 12/23/22 is valid
2. When I insert the formula to calculate via VBA it fails but when I insert the formula into the range as a formula it works? what could i be doing wrong?

Book2
	A	B	C	D	E	F
1	Formula	20221226
2	Value	#VALUE!
3
4
5
Sheet1

Cell Formulas
Range	Formula
B1	B1	=TEXT(WORKDAY(TODAY(),-1,[HOLIDAYS.xlsx]Sheet1!$B:$B),"YYYYMMDD")

HOLIDAYS.xlsx
	A	B	C
1	Christmas Day	12/26/2002	CLOSED
2	TEST	12/27/2022	TEST
3	New Year’s Day	1/1/2023	CLOSED
4	Martin Luther King, Jr. Day	1/18/2023	CLOSED
5	Presidents Day	2/15/2023	CLOSED
6	Good Friday	4/2/2023	CLOSED
7	Memorial Day	5/31/2023	CLOSED
8	Independence Day	7/5/2023	CLOSED
9	Labor Day	9/6/2023	CLOSED
10	Thanksgiving Day	11/25/2023	CLOSED
11	Christmas Day	12/24/2023	CLOSED
Sheet1

Option Explicit
Sub FormulaHolidays()

With Sheets("sheet1")
    .Range("B1").Formula = "=TEXT(WORKDAY(TODAY(),-1,[HOLIDAYS.xlsx]Sheet1!$B:$B),""YYYYMMDD"")"
    .Range("B2").Value = .[TEXT(WORKDAY(TODAY(),-1,[HOLIDAYS.xlsx]Sheet1!$B:$B),"YYYYMMDD"]
End With

End Sub

 

[steve the fish]

You would need 26th in your holiday range as well if im understanding.

 

[ItalianPlatinum]

It is, isnt it? B:B. I renamed the holidays sheet to sheet2 to avoid that confusion

=TEXT(WORKDAY(TODAY(),-1,'D:\User\Desktop\VBA TESTING\[HOLIDAYS.xlsx]Sheet2'!$B1:$B10000),"YYYYMMDD")

 

[steve the fish]

Oh ok i see the problem. You have 2002 not 2022.

 

[ItalianPlatinum]

Oh ok i see the problem. You have 2002 not 2022.

haha

i was going to say the formula should work!. Ok that solves #1. but still same issue with VBA

 

[steve the fish]

Well with what you have there i would just have your 2nd line as

.Range("B2").Value = .Range("B1").Value

 

[ItalianPlatinum]

So I have that as a workaround but I didnt know if there was a way to make it work. or do i need really to go the approach of putting the formula in the range then applying its value over it.

 

[steve the fish]

To be honest thats pretty much the way id always do it. Cant see the gain in the evaluate method here.

 

[ItalianPlatinum]

ok thanks