Specifying VBA Calculation for a particular Worksheet different from Active Sheet?

  • Hi!
    I have a simple VBA where values in Rows 1:10 of Col. A are divided by Values in Rows 1:10 of Col. B and result is given in Rows 1 to 10 of Col. C.
    This is code is run every 10 seconds:

    Code
    Sub Divide()
             Range("C1").Value = Range("A1").Value / Range("B1").Value
    Application.OnTime Now + TimeValue("00:00:10"), "Divide"
    End Sub


    The problem is:
    1. That the macro will run only on whatever is the current active sheet every 10 seconds, whereas I want it to run only on Sheet1.
    2. Only value in cell A1 is being divided with value in cell B1 and result appearing in cell C1, whereas I want calculations for Rows 1 to 10 of Col A and Col. B and respective result in Rows 1 to 10 of Col. C
    Any Ideas?
    PS: The values in col. A and B are being populated automatically from some other process and not entered manually, and result of Col. C is being used on another worksheet hence this requirement.

  • Re: Specifying VBA Calculation for a particular Worksheet different from Active Sheet


    Hi naira,
    Please try this (not tested):

    Code
    Sub Divide()
       Dim rg As Range, c As Range
       Set rg = Sheets(1).Range("C1:C10")
       For Each c In rg
          c.Value = c.Offset(0, -2).Value / c.Offset(0, -1).Value
       Next c
        Application.OnTime Now + TimeValue("00:00:10"), "Divide"
    End Sub
  • Re: Specifying VBA Calculation for a particular Worksheet different from Active Sheet


    Hi GC, Code works great and as per requirement, but if any of the cells in the range A1:B10 is blank during the macro run, it gives an error "Run Time Error '6': Overflow and highlights the code

    Code
    c.Value = c.Offset(0, -2).Value / c.Offset(0, -1).Value

    Can you please look into it again? Sample file with your code attached to check

  • Re: Specifying VBA Calculation for a particular Worksheet different from Active Sheet


    Like this ?


    Code
    Sub Divide()
        Dim rg As Range, c As Range
        On Error Resume Next
        Set rg = Sheets(2).Range("C1:C10")
        For Each c In rg
            c.Value = c.Offset(0, -2).Value / c.Offset(0, -1).Value
        Next c
        Application.OnTime Now + TimeValue("00:00:10"), "Divide"
        On Error GoTo 0
    End Sub
  • Re: Specifying VBA Calculation for a particular Worksheet different from Active Sheet


    Quote from GCExcel;679748

    Like this ?


    Love IT!
    Works EXACTLY as per requirement
    Thanks a Lot!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!