Mathematical range operations in VBA

  • I've attempted searches on the topic and cannot come up with anything....


    I'm attempting to conditionally alter specific column range values based on the variable 'tagrow'. From the example you can see that if 'tagrow' is 7 or 8, I want to divide the values in column C by 1000 and put them in column D. If tagrow was different, I will use another conditional statement with a different denominator.


    In the current code, I get a type mismatch error. I have also tried dividing by an equal size column range full of 1000's, with no luck (matrix math anyone?). All I want to do is manipulate ranges!


    Thanks in advance.



    Code
    'Initializing tagrow so we can test what train we are trending'
    tagrow = Cells(12, 2).Value
    
    
    'Only Propylene flows need adjustment in A and B trains, calculate everything'
    If tagrow = 7 Or tagrow = 8 Then
        Sheet2.Calculate
        Range("D15:D115").Value = Range("C15:C115").Value / 1000  
    End If
  • Re: Mathematical range operations in VBA


    I think that you want
    D15=C15/1000
    D16=C16/1000
    etc


    Code
    Range("D15")= Range("C15") / 1000
    Range("D15:D115").Filldown


    Note that this will put the values in and not the equation.


    HTH,


    Alan.
    PS Is there a reason this can not be an if statement in the cell?
    A.

  • Re: Mathematical range operations in VBA


    Hey mate,


    Just to clarify.


    Do you want to divide the sum of all values in column C by 1000 and put one answer in column D or do you wish to divide each cell in column c by 1000 and put the respective value in column d?


    If it is the latter i would suggest writing something like this



    Hope this helps :)

  • Re: Mathematical range operations in VBA


    mooreydp,


    your code works exactly as intented. I never thought of putting a simple formula in and then converting to the number.


    trying to go from step 1 to step 3 is much more difficult than 1,2,3.


    Thanks!


    NT

Participate now!

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