VBA Insert Formula Into Each Cell In Range

  • Hi, I wonder whether someone may be able to help me please.


    Contained within a workbook, I have a sheet called "Team" with columns C:O and a dynamic number of rows within this range.
    Could someone perhaps tell me please how I may add the following formula to every cell within the range:

    Quote

    =100-'The Cell Value'


    Many thanks and kind regards

  • Re: VBA Insert Formula Into Each Cell In Range


    Do you mean calculate the value of 100 - Cell Value - otherwise it does not make sense - you would get a circular reference e.g. in A1 cannot have a formula = 100-A1 - it would be 0?

    Regards
    [SIZE=3]Anthony
    [/SIZE]​[SIZE=3]
    [/SIZE]​​​[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




  • Re: VBA Insert Formula Into Each Cell In Range


    Code
    1. Sub LoopMethod()
    2. Dim myRng As Range
    3. Dim ws As Worksheet
    4. Set ws = Worksheets("Team")
    5. Set myRng = ws.Range("C1").CurrentRegion
    6. For Each cell In myRng
    7. cell.Value = 100 - cell.Value
    8. Next
    9. End Sub


    OR

    Regards
    [SIZE=3]Anthony
    [/SIZE]​[SIZE=3]
    [/SIZE]​​​[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




  • Re: VBA Insert Formula Into Each Cell In Range


    No problem. Your welcome :)

    Regards
    [SIZE=3]Anthony
    [/SIZE]​[SIZE=3]
    [/SIZE]​​​[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]