Divide by VBA

billandrew

Well-known Member
Joined
Mar 9, 2014
Messages
743
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]
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
.


[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]
 
Upvote 0
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

Code:
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
 
Upvote 0
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
Code:
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
 
Upvote 0
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:

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

Forum statistics

Threads
1,226,729
Messages
6,192,696
Members
453,747
Latest member
tylerhyatt04

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top