Division

[SGlobal]

I need to divide the values of each cell in column A with the corresponding cell in Column b with VBA. how can I do that?

so say I have these values
A B C
1 3 1/3
2 2 2/2
3 1 3/1

Column C should = 1/3 C1, 2/2 for C2, and so on and so forth

I have a full list of these how can I do that with a macro?

 

[Lidsavr]

SGlobal,
in C1 type =B1/A1 and press enter.

 

[Scott Huish]

Do you just want it to say 1/3 or do you want it to actually divide it out?

 

[Lidsavr]

SbGlobal,
Sorry about my first reply, in the middle of writing out the macro, I pressed the wrong key and kicked myself into another screen.

It looks like you have someone helping you already. Best of luck.

 

[Scott Huish]

If it is to display the text:

Sub test()
Range("C:C").NumberFormat = "General"
With Range("C1:C" & Range("A" & Rows.Count).End(xlUp).Row)
    .Formula = "=A1&""/""&B1"
    .NumberFormat = "@"
    .Value = .Value
End With
End Sub

If it's to divide it out:

Sub test()
Range("C:C").NumberFormat = "General"
With Range("C1:C" & Range("A" & Rows.Count).End(xlUp).Row)
    .Formula = "=A1/B1"
    .Value = .Value
End With
End Sub

 

[mikerickson]

You could both return the value and show the fraction

Dim oneCell as Range

For each oneCell in Range("A1:A10")
    With oneCell
        .Offset(0,2).Value = .Value / .Offset(0,1).Value
        .Offset(0,2).NumberFormat = Chr(34) & CStr(.Value) & "/" & CStr(.Offset(0,1).Value) & Chr(34)
    End With
Next oneCell

 

[SGlobal]

These codes are all great thank you!

 

[Scott Huish]

<HR style="BACKGROUND-COLOR: #ffffff; COLOR: #ffffff" SIZE=1>

You could both return the value and show the fraction

Depending on the length of the actual list you could run out of available number formats.

 

[mikerickson]

<HR style="BACKGROUND-COLOR: #ffffff; COLOR: #ffffff" SIZE=1>

Depending on the length of the actual list you could run out of available number formats.

True. Hmm...other than looping through cells, is there some way to count the number of number formats in use by a particular workbook? I can't think of one.