Solved: Last business day in formula

[Lartk]

See the below VBA code. In the formula, is there a way to reference the last business day instead of having to manually change the date everyday? So instead of ...,"11/27/2012","11/27/2012" is there a way to reference the last business day in this formula? See the attached sheet, the date is also in cell A1 we could also reference the date in that cell too in the formula if there is a way to do that?

[VBA]Sub Macro15()
'
ActiveCell.FormulaR1C1 = _
"=BDH(RC[-5]&"" equity"",""px last"",""11/27/2012"",""11/27/2012"")"
Range("G3").Select
End Sub
[/VBA]

[Bob Phillips]

Use

=BDH(B3&" equity","px last",WORKDAY(DATE(YEAR($A$1),MONTH($A$1)+1,0),-1),WORKDAY(DATE(YEAR($A$1),MONTH($A$1)+1,0),-1))

[Lartk]

I am getting an application defined or object defined error for the below formula

.Cells(i, "G").FormulaR1C1 = _
"=BDH(B3&"" equity"",""px last"",WORKDAY(DATE(YEAR($A$1),MONTH($A$1)+1,0),-1),WORKDAY(DATE(YEAR($A$1),MONTH($A$1)+1,0),-1))"

[Teeroy]

Change .FormulaR1C1 to .Formula as you are not using R1C1 format in the formula anymore.

[Lartk]

.Cells(i, "G").Formula = _
"=BDH(B3&"" equity"",""px last"",WORKDAY(DATE(YEAR($A$1),MONTH($A$1)+1,0),-1),WORKDAY(DATE(YEAR($A$1),MONTH($A$1)+1,0),-1))"

For some reaosn this formula does not work. If I have the date in cell A1 that needs to be used, can I somehow reference the date in cell A1 in place of "11/27/2012" in the below code?

Sub Macro15()
'
ActiveCell.FormulaR1C1 = _
"=BDH(RC[-5]&"" equity"",""px last"",""11/27/2012"",""11/27/2012"")"
Range("G3").Select
End Sub

[Lartk]

The date in cell A1 already has the date we need, so I am trying a fromula like this but it doesnt seem to work..

=BDH(B2&" equity","px_last","WORKDAY($A$1,0,0)","WORKDAY($A$1,0,0)")

I think is may be because the result of "WORKDAY($A$1,0,0)" does not return in date form like 11/27/2012 so the forumla cant read what date it is...

[SamT]

"R1C1" = "$A$1"
"RC" = ThisCell
"R[-1]C" = ThisCell.Offset(-1, 0)
"R3[+1]C1" =Cells($A$3).Offset(1, 0)

[Lartk]

What will that do? Should I add this in the code somewhere?

[Lartk]

And right now I am not working with R1C1, I am working with the below code:

[VBA]Sub Test()
Dim NumBlocks As Long
Dim Lastrow As Long
Dim i As Long

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "F").End(xlUp).Row
For i = 3 To Lastrow

.Cells(i, "G").Formula = _
"=BDH(RC[-5]&"" equity"",""DATE(MONTH($A$1)YEAR($A$1)"",""DATE(MONTH($A$1)YEAR($A$1)"")"
Range("G3").Select

Next i

End With
End Sub
[/VBA]

[Lartk]

Right now, the below code works perfectly. I just need to get the dates (3/12/2013) to reference the date in cell A1 so I dont have to manually go in everyday and enter the date.

[VBA]Sub Test()
Dim NumBlocks As Long
Dim Lastrow As Long
Dim i As Long

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "F").End(xlUp).Row
For i = 3 To Lastrow

.Cells(i, "G").Formula = _
"=BDH(RC[-5]&"" equity"",""px_last"",""3/12/2013"",""3/12/2013"")"
Range("G3").Select

Next i

End With
End Sub
[/VBA]

[SamT]

For some reaosn this formula does not work. If I have the date in cell A1 that needs to be used, can I somehow reference the date in cell A1 in place of "11/27/2012" in the below code?

Sub Macro15()
'
ActiveCell.FormulaR1C1 = _
"=BDH(RC[-5]&"" equity"",""px last"",""11/27/2012"",""11/27/2012"")"
Range("G3").Select
End Sub

looks like R1C1 to me.

[SamT]

[vba]Sub Test()
Dim NumBlocks As Long
Dim Lastrow As Long
Dim i As Long
Dim RefersToA
RefersToA = CStr(Range"A1"))

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "F").End(xlUp).Row
For i = 3 To Lastrow

.Cells(i, "G").Formula = _
"=BDH(RC[-5]&"" equity"",""px_last""," & REfersToA & "," & RefersToA & ")"
Range("G3").Select

Next i

End With
End Sub [/vba]

[SamT]

Never mind I see the answer in one of your other posts.

[SamT]

Never mind again. Got in a hurry and edited the wrong post before, so did some more editing to make things right.

[Teeroy]

It appears your BDH function is expecting strings for the 3rd and fourth terms so you need to enclose the value of A1 with "". Try changing the formula line in SamT's code to:
[vba].Cells(i, "G").Formula = _
"=BDH(RC[-5]&"" equity"",""px_last"",""" & REfersToA & """,""" & RefersToA & """)" [/vba]

[SamT]

You're right, I left the "As String" off the Variable declaration.

However, I have never had to enclose string variables in quotes when passing them as arguments. His UDF may require them, so it's worth a try.

[Teeroy]

@SamT, if you were calling the function directly from VBA you are right you wouldn't need the quotes since the variable is of data type string. Because it is not a direct function call the formula string will evaluate (and concatenate) first then call the UDF. That formula string may just show those arguments as dates to the UDF which is why I suggested the quotes.

[Lartk]

I tried this code but there is a syntax error with
RefersToA = CStr(Range"A1"))


[VBA]Sub Test()
Dim NumBlocks As Long
Dim Lastrow As Long
Dim i As Long
Dim RefersToA
RefersToA = CStr(Range"A1"))

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "F").End(xlUp).Row
For i = 3 To Lastrow

.Cells(i, "G").Formula = _
"=BDH(RC[-5]&"" equity"",""px_last"",""" & RefersToA & """,""" & RefersToA & """)"
Range("G3").Select

Next i

End With
End Sub
[/VBA]

[SamT]

Since we don't have your workbook and UDFs, I sometimes write simple code directly into the Post Reply box, expecting the reader to catch simple errors. If I had written it in the VBE, it would have caught it.

If you have the VBE set up to automatically place "Option Explicit" at the top of Code Pages, VBE would have told you it expected a Parenthesis in that line.
[vba]RefersToA = CStr(Range"A1"))[/vba]
is missing a parenthesis.

It should read
[vba]RefersToA = CStr(Range("A1"))[/vba]
You could also use
[vba]RefersToA = CStr(Range("A1").Value)[/vba]
but .Value is the default return for a Range, so I usually leave it off.

[Lartk]

That works great, thank you!!