Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Jun 2015
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Relative to Absolute Macro for If Statements

    Hey guys,

    I have a large excel workbook where I have to change the cells from relative to absolute formulas.

    I used the following VBA for one of my sheets as a test run:
    Code:
    Sub ConvertToAbsolute() 
    For Each cell In ActiveSheet.UsedRange 
    If cell.HasFormula = True Then 
    cell.Formula = Application.ConvertFormula _ 
    (Formula:=cell.Formula, _ 
    FromReferenceStyle:=xlA1, _ 
    ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute) 
    End If 
    Next 
    End Sub 
    

    This worked for the first sheet, however, my second sheet is full of If Statements and it had returned back #VALUE! errors

    Example:
    =IF(ISERROR(Conversions!G7/Clicks!G7),"",Conversions!G7/Clicks!G7)

    Does anyone know an alternative to change these cells from relative to absolute? Again, this is a very large workbook, so I'm just trying to find something (such as a macro) that can change all these cells at once.
    Last edited by RetiredGeek; 2015-06-03 at 15:05. Reason: Added Code Tags

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Karen,

    Welcome to the Lounge as a new Poster!

    I tried your macro on a sheet with a #Value error and it made the conversion w/o a problem.

    Have you tried debugging the code and seeing exactly which cell the code is choking on?

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Jun 2015
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts
    RetiredGeek,

    Thanks for the welcome!!

    That's interesting that it is working for you because this is what happens when I run the macro:
    Screen Shot 2015-06-03 at 1.53.32 PM.png

    I'm also not entirely sure on what you mean by debugging the code. I read some articles, and they mention that I have to 'step-in' to the code, but all it does is highlight the line of the code in yellow?

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Karen,

    The #Value errors mean that Excel can not calculate a numeric value for a numeric function. Where there good values on the worksheet before your ran the macro? Do the formulas reference any cells in another worksheet which may have changed?

    Could you possibly post a copy of your workbook for us to look at? It would surely make solving the problem much easier.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    New Lounger
    Join Date
    Jun 2015
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Attached Copy of Workbook in Attachment as Zip

    Quote Originally Posted by RetiredGeek View Post
    Karen,

    The #Value errors mean that Excel can not calculate a numeric value for a numeric function. Where there good values on the worksheet before your ran the macro? Do the formulas reference any cells in another worksheet which may have changed?

    Could you possibly post a copy of your workbook for us to look at? It would surely make solving the problem much easier.

    HTH
    RetiredGeek,

    Yes, you can see in the file that there are values generated from the If Statements in earlier months (January-March).

    The sheets that you want to be looking at are the blue ones (we've been specifically testing on the sheet labeled Conversion Rate). We've tried the macro on the orange sheet that says 2015 Schedule, and that worked. We just don't understand why its not working on these blue sheets. We don't want to go through each cell and manually add in $.

    I attached a copy of our workbook! Thanks for the help!
    Attached Files Attached Files

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Karen,

    Still at a loss all 3 sheets converted just fine.

    I did make some changes to your macro to make it run faster after doing the Conversion Rate sheet but nothing that affects the process:
    Code:
    Option Explicit
    
    Sub ConvertToAbsolute()
    
       Dim cell As Range
       
       With Application
           .Calculation = xlCalculationManual
           .ScreenUpdating = False
       End With
       
       For Each cell In ActiveSheet.UsedRange
          If cell.HasFormula = True Then
            cell.Formula = Application.ConvertFormula _
              (Formula:=cell.Formula, _
               FromReferenceStyle:=xlA1, _
               ToReferenceStyle:=xlA1, _
               ToAbsolute:=xlAbsolute)
          End If
       Next
       
       With Application
           .Calculation = xlCalculationAutomatic
           .ScreenUpdating = True
       End With
       
    End Sub    'ConvertToAbsolute()
    Results File: Test RG Results V1.zip

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    New Lounger
    Join Date
    Jun 2015
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts
    That's really weird!
    We ran it on excel for Mac and a PC and they both came up with the #Value errors. I even copied your exact code and ran it, and it still gave me the same errors.

    So please correct me if I am wrong going through the process:
    I go to Macros and clicked Visual Basic Editor. Click on the sheet that I want to run it on, and insert the code for the macro. And then I clicked run macro.

    What specific steps did you take?

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Karen,

    Not quite.

    This is a general purpose macro and as such should be in a general module not a sheet module!

    If you look at the file I attached to post #6 you'll find my code under Module1.

    To get it there you Click on Insert->Module from the menus in the VBE then paste or write code there.
    To run the code you select the worksheet tab you want to run it on then press Alt+F8 and select the macro from the list.

    Basically you only put Event handling code in the Sheet and This Workbook modules.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. The Following User Says Thank You to RetiredGeek For This Useful Post:

    karenq (2015-06-04)

  10. #9
    New Lounger
    Join Date
    Jun 2015
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts
    RetiredGeek,

    Oh that makes sense now! Thank you so much for the help!

Tags for this Thread

Posting Permissions

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