Need a Macro for division

[talisman77]

I have a column of 480 numbers that needs to be divided by the column to the right of it also containing 480 numbers.

I don't know how to write a macro so any help would be greatly appreciated!

Thanks,
Bob

 

[kpark91]

Hi, talisman77.

You will need to use something called "for loop" for this.
It basically iterates through specified range one by one and outputs your desired result if coded correctly.

The syntax of "for loop" is

For i = 1 To 10
    'Your Code
Next i

So, in this example, i is changing everytime it iterates.
(1, 2, 3,.... ,10)

Anyways, try this code

Sub kpark91July18()
    Dim i As Long
    For i = 1 To 480
        Range("C" & i).Value = CDbl(Range("A" & i).Value) / CDbl(Range("B" & i).Value)
    Next i
End Sub

This will divide column A by column B from row 1 To 480 and output results in your column C.

 

[talisman77]

Thank you for getting back to me kpark91!

I tried the code but I get a run time error "6" overflow.......

When I hit "debug" it takes me to the code and highlights this line....

Range("C" & i).Value = CDbl(Range("A" & i).Value) / CDbl(Range("B" & i).Value)

I put the whole code in the VBA but maybe that isn't where it was supposed to go.....I am not real good with Excel, I just know what I want it to do but don't know how to make it do it

 

[kpark91]

Hi, Bob.

Try this then

Range("C" & i).Value = Range("A" & i).Value / Range("B" & i).Value

 

[Comfy]

What's wrong with =A1/B1 entered in C1 and then copied down?

Or do you mean the total of column A divided by the Total of column B =(SUM(A1:A480)/SUM(B1:B480))

 

[mikerickson]

The formula =A1/B1 dragged down will do what you want.
Whether the formula or VBA is better depends on what you are going to do with these results?

Regarding the Overflow error, what kinds of numbers are in these columns? Very large? Very small?

 

[talisman77]

Kpark91....that one didn't work either.....

Comfy...it is not the total of column A divided by the total of column B. It is the taking each individual cell in column A and dividing it by the cell in Column B to give me 480 separate answers in column C.....it is actually 408 not 480...

mikerickson....I apologize but I don't how to use the formula...I am willing to try if you could explain a little how to use it.....The numbers will be something like 320/14 or 70/6......nothing real huge

 

[Comfy]

Comfy...it is not the total of column A divided by the total of column B. It is the taking each individual cell in column A and dividing it by the cell in Column B to give me 480 separate answers in column C.....it is actually 408 not 480...

Use the other formula that was posted.

C1 would be

=A1/B1

 

[talisman77]

I am at a loss here Comfy....I don't how to use the formula....

 

[Comfy]

I am at a loss here Comfy....I don't how to use the formula....

We are assuming your data starts in Cells A1 and B1

so in cell C1 type

=A1/B1

With Cell C1 active hover your mouse over the bottom right corner of the cell and you mouse pointer will change to a smaller cross.

Double click the left mouse button and this will copy the formula down column C until it reaches a blank in column B