Trouble Using Formulas in VBA
Results 1 to 9 of 9

Thread: Trouble Using Formulas in VBA
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2019
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Trouble Using Formulas in VBA

    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

  2. #2
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,103
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Trouble Using Formulas in VBA

    Quote Originally Posted by ohFice View Post
    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 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
    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.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  3. #3
    New Member
    Join Date
    May 2019
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Trouble Using Formulas in VBA

    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?

  4. #4
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    10,996
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Trouble Using Formulas in VBA

    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 by MARK858; Aug 22nd, 2019 at 12:44 AM.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  5. #5
    New Member
    Join Date
    May 2019
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Trouble Using Formulas in VBA

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

  6. #6
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    10,996
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Trouble Using Formulas in VBA

    Post the code as you now have it and tell us what line it errors out on for you.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  7. #7
    New Member
    Join Date
    May 2019
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Trouble Using Formulas in VBA

    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

  8. #8
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,694
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Trouble Using Formulas in VBA

    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
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  9. #9
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    10,996
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Trouble Using Formulas in VBA

    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, Off_Since.Value)
    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(Off_TC, , 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
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •