Trouble Using Formulas in VBA

ohFice

New Member
Joined
May 30, 2019
Messages
24
Hi,

I have a specific problem with some code, and then a general question to ask as well. I started learning/writing VBA code about 4 months ago; therefore, I apologize for my ignorance on a lot of this stuff; anyways, my general question would be,

1. Is there any general / simple way to use formulas in VBA? I've seen people use the Evaluate, as well as other methods, and am just confused as to when? and how? to use these methods. ; if you do not quite understand my question, my specific code question below gives a perfect example.





2. I am working on the code below, and can't get the DateDiff function to work, anybody know what I'm doing wrong?

Code:
Sub Assumed_Retention()

Application.ScreenUpdating = False


Dim LRA_WB As Workbook, Off_TC As String
Dim LRA_WS, LRA_WS2 As Worksheet
Dim Off_SC, ProformaDate, Off_SinceSC, Off_Since, Off_Retention, Off_RetentionSC, Off_Calc, C As Range


Set LRA_WB = ThisWorkbook
Set LRA_WS = LRA_WB.Worksheets("Rent_Roll")
Set LRA_WS2 = LRA_WB.Worksheets("List")


Set Off_SinceSC = LRA_WS.Range("E:E").Find(Off_TC, , xlValues, xlWhole).Offset(-1, 15)
Set Off_Since = Range(Off_SinceSC, Off_SinceSC.End(xlUp))
Off_TC = "Total Office"
    
Set Off_RetentionSC = LRA_WS.Range("E:E").Find(Off_TC, , xlValues, xlWhole).Offset(-1, 12)
Set Off_Retention = Range(Off_RetentionSC, Off_RetentionSC.End(xlUp))
Set ProformaDate = LRA_WS.Range("F11")
Off_Calc = Evaluate("DateDiff(" & ProformaDate.Address, Off_Since.Address, " & m & ")


For Each C In Off_Calc


    If C > 9.99 Then
    
        Off_Retention = 0.75
        
    ElseIf C > 4.99 Then
    
        Off_Retention = 0.5
        
    ElseIf C > 2.99 Then
    
        Off_Retention = 0.25
        
    End If


Next C


Application.ScreenUpdating = True


End Sub
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
2. I am working on the code below, and can't get the DateDiff function to work, anybody know what I'm doing wrong?

Code:
Off_Calc = Evaluate("DateDiff(" & ProformaDate.Address, Off_Since.Address, " & m & ")
The Excel function is spelled DateDif (only one "f" at the end)... I didn't really parse your code in full, but if you make that change in your code, I think it should work. Just so you know, VBA has its own function named DateDiff (yes, it is what you tried to use inside of the Evaluate function) that you can call directly to get the number of months...
Code:
Off_Calc = DateDiff("m", ProformaDate.Value, Off_Since.Value)

A note about your Dim statements... in VBA, each variable must be individually declared for its Type or else it will be defaulted to a Variant. So, in this code line...
Code:
Dim Off_SC, ProformaDate, Off_SinceSC, Off_Since, Off_Retention, Off_RetentionSC, Off_Calc, C As Range
only C is being declared as a Range variable... all of the other variable in the statement are being declared as Variants (which I think may be correct for your Off_Calc variable). The correct way to declare all of them except Off_Calc as Range variables is like this...
Code:
Dim [B][COLOR="#FF0000"]Off_SC As Range, [/COLOR][/B]ProformaDate As Range, Off_SinceSC As Range, Off_Since As Range, Off_Retention As Range, Off_RetentionSC As Range, Off_Calc As Variant, C As Range
On a quick look, I do not see where the variable I highlighted in red is being used within your code, so I am guessing you intended it to be a Range.
 
Upvote 0
Hi Rick,

Thanks for the reply, and for your time.

Unfortunately that did not fix the issue; I adjusted my Dim statement, and set my Off_Calc variable to your suggested code, and getting a Type mismatch error.

Any idea why?
 
Upvote 0
Did you try the change below?

The Excel function is spelled DateDif (only one "f" at the end)... I didn't really parse your code in full, but if you make that change in your code, I think it should work.
 
Last edited:
Upvote 0
Yeah didn't work either, I think my evaluate was all messed up, its so hard to use for me :/
 
Upvote 0
Post the code as you now have it and tell us what line it errors out on for you.
 
Upvote 0
Code:
Sub Assumed_Retention()

Application.ScreenUpdating = False


Dim LRA_WB As Workbook, Off_TC As String
Dim LRA_WS As Worksheet, LRA_WS2 As Worksheet
Dim Off_SC As Range, ProformaDate As Range, Off_SinceSC As Range, Off_Since As Range, Off_Retention As Range, Off_RetentionSC As Range, Off_Calc As Variant, C As Range


Set LRA_WB = ThisWorkbook
Set LRA_WS = LRA_WB.Worksheets("Rent_Roll")
Set LRA_WS2 = LRA_WB.Worksheets("List")


Set Off_SinceSC = LRA_WS.Range("E:E").Find(Off_TC, , xlValues, xlWhole).Offset(-1, 15)
Set Off_Since = Range(Off_SinceSC, Off_SinceSC.End(xlUp))
Off_TC = "Total Office"
    
Set Off_RetentionSC = LRA_WS.Range("E:E").Find(Off_TC, , xlValues, xlWhole).Offset(-1, 12)
Set Off_Retention = Range(Off_RetentionSC, Off_RetentionSC.End(xlUp))
Set ProformaDate = LRA_WS.Range("F11")
Off_Calc = DateDiff("m", ProformaDate.Value, Off_Since.Value)


For Each C In Off_Calc


    If C > 9.99 Then
    
        Off_Retention = 0.75
        
    ElseIf C > 4.99 Then
    
        Off_Retention = 0.5
        
    ElseIf C > 2.99 Then
    
        Off_Retention = 0.25
        
    End If


Next C


Application.ScreenUpdating = True


End Sub


there's a mismatch error on the DateDif formula line, and not sure if anything past that is bugged
 
Upvote 0
It looks like you are trying to do a datediff between Range("F11") against a range of cells

Code:
Off_Calc = DateDiff("m", ProformaDate.Value, Off_Since.Value)

If you want to do that you will need to loop through each cell in Off_Since
 
Upvote 0
As Michael has stated (posted quicker than me)

Code:
Set Off_Since = Range(Off_SinceSC, Off_SinceSC.End(xlUp))


is creating a multicell range


but you are using it in Datediff which requires a single date in the below line

Code:
Off_Calc = DateDiff("m", ProformaDate.Value, [B]Off_Since.Value[/B])

Also I can't see how
Code:
Off_TC = "Total Office"

shouldn't be higher up as you are using it in

Code:
Set Off_SinceSC = LRA_WS.Range("E:E").Find([B]Off_TC[/B], , xlValues, xlWhole).Offset(-1, 15)


but then you will then also get an issue with the line below as Off_Calc is a number when it needs to be an Object


Code:
For Each C In Off_Calc
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top