Cell = ActiveCell.Offset(0,1) Divided by ActiveCell.Offset(0,-1 3

[APElliott]

Hello,

How do you write the following in code:

I want a cell value to equal the cell to the right of it divided by the cell to the left of it.

Simple, but beyond me.

Thankyou,

Andrew

 

[Molby]

You can use:

ActiveCell.FormulaR1C1 = "=RC[1]/RC[-1]"

Regards,

Ian

 

[VBAva]

Try this and see if it works

Range("B2).Select 'Whatever cell you want
ActiveCell.FormulaR1C1 = "=RC[1]/RC[-1]"

 

[VBAva]

sorry ian
you got there just before me

great minds and all the rest

 

[Bowers74]

As you can see Andrew, I like variables.

Sub Divide()
Dim cLeft As Range
Dim cRight As Range
Dim cAct As Range
Set cAct = ActiveCell
Set cLeft = cAct.Offset(0, -1)
Set cRight = cAct.Offset(0, 1)

    If IsNumeric(cRight) And IsNumeric(cLeft) Then
        cAct = cRight / cLeft
    Else
        cAct = "Indivisable"
    End If
    
Set cAct = Nothing
Set cLeft = Nothing
Set cRight = Nothing
End Sub

You can add a loop to this later if you want to go down a column. (I know you can) ;-)

Enjoy!!


Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[APElliott]

Fantansic - Cheers,

Just ran it and it works.

Except if the cell to the right = "inc".

How can I code =if(RC[1]="inc","inc",RC[1]/RC[-1])

Thanks

Andrew

 

[SkipVought]

Hi,

One problem. What if the cell on the left is equal to zero?

Function RightDivByLeft(Rng as Range) as Single
   Dim nLeft as Single, nRight as Single
   nLeft = Rng.Offset(0, -1).Value
   nRight = Rng.Offset(0, 1).Value
   If nLeft = 0 Then
      RightDivByLeft = 0
   Else
      RightDivByLeft = nRight / nLeft
   End If
End Function

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com