Divide by VBA

[billandrew]

Have data in column A & B. Need to divide column B by column A in column C. The numbers are percentages and the range is dynamic may change.

example

[TABLE="width: 216"]
<colgroup><col width="72" span="3" style="width:54pt"> </colgroup><tbody>[TR]
[TD="width: 72, align: right"]40[/TD]
[TD="width: 72, align: right"]12[/TD]
[TD="class: xl63, width: 72, align: right"]30.0%[/TD]
[/TR]
[TR]
[TD="align: right"]35[/TD]
[TD="align: right"]11[/TD]
[TD="class: xl63, align: right"]31.4%[/TD]
[/TR]
[TR]
[TD="align: right"]23[/TD]
[TD="align: right"]10[/TD]
[TD="class: xl63, align: right"]43.5%[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]11[/TD]
[TD="class: xl63, align: right"]78.6%[/TD]
[/TR]
</tbody>[/TABLE]

 

[Logit]

.


[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

1​

[/td][td]

40​

[/td][td]

12​

[/td][td]

30.00%​

[/td][td]

<---------​

[/td][td]Formula[/td][td]=B1/A1[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

2​

[/td][td]

35​

[/td][td]

11​

[/td][td]

31.43%​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3​

[/td][td]

23​

[/td][td]

10​

[/td][td]

43.48%​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4​

[/td][td]

14​

[/td][td]

11​

[/td][td]

78.57%​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[Scott T]

As Logit posted above you can just use a formula and copy down as need.

If you must have VBA then this should work. Note. this does not test if you are dividing by zero so you may get DIV/0! errors in column C

Sub divAB()
Dim lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
Range("C2:C" & lr).FormulaR1C1 = "=RC[-1]/RC[-2]"

End Sub

 

[billandrew]

This is great. How can I also convert the errors to a zero & the range to a %

 

[billandrew]

Could you explain "=RC[-1]/RC[-2]"

I have change the columns. I would like to adjust.

 

[Scott T]

That is R1C1 cell referencing RC[-1] is saying stay on the same row but go one column to the left. Since this is in column C this goes to column B, a positive number would move to the right.


This should return 0 instead of a div/0 error

Sub divAB()
Dim lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
Range("C2:C" & lr).FormulaR1C1 = "=iferror(RC[-1]/RC[-2],0)"

End Sub

 

[billandrew]

awesome

I'll try to adjust to my columns...

Thank You

 

[joeu2004]

Could you explain "=RC[-1]/RC[-2]". I have change the columns. I would like to adjust.

It might not be necessary to use the R1C1 celll reference form, especially if it is unfamiliar to you. You can just use relative, semi-absolute and absolute cell references just as you would in Excel. For example:

Sub divAB()
Dim lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
Range("C2:C" & lr).Formula = "=B2/A2"
End Sub

 

[billandrew]

Is easier, however does not solev the #div/0 error

Thanks

 

[billandrew]

Adjusted the RC[-1]/RC[-2] to my columns.

Worked perfect

Thanks Scott