Trouble Using Formulas in VBA

[ohFice]

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?

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

 

[Rick Rothstein]

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

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...

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...

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...

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.

 

[ohFice]

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?

 

[MARK858]

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.

 

[ohFice]

Yeah didn't work either, I think my evaluate was all messed up, its so hard to use for me :/

 

[MARK858]

Post the code as you now have it and tell us what line it errors out on for you.

 

[ohFice]

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

 

[Michael M]

It looks like you are trying to do a datediff between Range("F11") against a range of cells

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

 

[MARK858]

As Michael has stated (posted quicker than me)

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

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

Also I can't see how

Off_TC = "Total Office"

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

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

For Each C In Off_Calc