PDA

View Full Version : Solved: Last business day in formula



Lartk
11-28-2012, 01:25 PM
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?

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

Bob Phillips
11-28-2012, 02:26 PM
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
11-28-2012, 03:19 PM
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
11-28-2012, 10:45 PM
Change .FormulaR1C1 to .Formula as you are not using R1C1 format in the formula anymore.

Lartk
03-13-2013, 02:06 PM
.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
03-13-2013, 02:25 PM
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
03-13-2013, 02:42 PM
"R1C1" = "$A$1"
"RC" = ThisCell
"R[-1]C" = ThisCell.Offset(-1, 0)
"R3[+1]C1" =Cells($A$3).Offset(1, 0)

Lartk
03-13-2013, 02:46 PM
What will that do? Should I add this in the code somewhere?

Lartk
03-13-2013, 02:50 PM
And right now I am not working with R1C1, I am working with the below code:

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

Lartk
03-13-2013, 02:54 PM
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.

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

SamT
03-13-2013, 03:30 PM
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
03-13-2013, 03:38 PM
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

SamT
03-13-2013, 03:57 PM
Never mind I see the answer in one of your other posts.

SamT
03-13-2013, 04:08 PM
Never mind again. Got in a hurry and edited the wrong post before, so did some more editing to make things right.:banghead:

Teeroy
03-13-2013, 07:42 PM
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:
.Cells(i, "G").Formula = _
"=BDH(RC[-5]&"" equity"",""px_last"",""" & REfersToA & """,""" & RefersToA & """)"

SamT
03-13-2013, 09:22 PM
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
03-13-2013, 10:40 PM
@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
03-14-2013, 06:34 AM
I tried this code but there is a syntax error with
RefersToA = CStr(Range"A1"))


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

SamT
03-14-2013, 08:17 AM
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.
RefersToA = CStr(Range"A1"))
is missing a parenthesis.

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

Lartk
03-14-2013, 09:41 AM
That works great, thank you!!