Calculating the remaining percentage RRS feed

  • Question

  • Hi,

         I have a total number of servers in one column and will add every month a new column like pending servers for this month and i need to display the percentage of completed servers in a column. How can i do this.


      A1:  Total = 10 servers

       B1: September pending = 7 server

       C1: Oct Pending = 4 server

      D1: Percentage = 82.5% (=A1-B1\C1)

    Like this I will add for November month. When i add november month(D1) server detail it should calculate the completed percentage(E1) and it should be displayed automatically. How can i acheive this?

    Thursday, November 19, 2015 5:20 AM


  • Hi winoa,

    For this requirement, you could get the last used cell by using Range.End property, then get previous/next cell by using Range.Offset property.

    To add column or row, we could use Range.EntireColumn property or Range.EntrieRow property.

    To set formula value to the range, we could use Range.Formula property.

    A simple sample:

    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim r As Range
    Dim rc As Range
    Dim f As String
    Set r = ws.Cells(ws.Rows.Count, 1).End(xlUp)
    'Set r = ws.Range("B:B").End(xlDown)
    Set rc = r.Offset(1, 0)
    Set r = r.Offset(-1, 0)
    f = "=" & r.Address
    rc.Formula = f



    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, November 20, 2015 6:30 AM