Dividing not adjacent cells using VBA

Question

Any suggestions of how to solve this problem?

(Updated)

numerator,denominator are ranges containing integer values.

I want to do a division cell by cell of these ranges, e.g:

numerator(1)/denominator(1) numerator(2)/denominator(2) ...

and paste the results in quotient. All ranges are of same size and are row-vector dimensioned.

My solution attempt:

Sub DivRanges()
    Dim targetRng As Range, sourceRng1 As Range, sourceRng2 As Range    
    For i = 1 To targetRng.Cells.Count
         quotient.Cells(i).Value = numerator.Cells(i).Value / denominator.Cells(i).Value
    Next i
End Sub

The problem is that DivRanges doesn't loop through each value of the ranges since they are row-vectors. Instead it goes horisontally one step down as i grows. This problem occurs when the cells of the ranges are not adjacent.

Here's a display of the problem based on code from mehow . The result are the cells selected in the sheet.

Example

row-vectors? what do they look like in a cell? Can you provide a screenshot or sample? is there a few values in a single cell? please explain — user2140173

Unknown Unknown · Accepted Answer · 2013-07-25T11:02:33

if your spreadsheet looks like

bef

then this code

Sub DivRanges()
    Dim quotient As Range, numerator As Range, denominator As Range
    
    Set numerator = Range("A1:D1")
    Set denominator = Range("A2:D2")
    Set quotient = Range("A3:D3")
    
    Dim i As Long
    For i = 1 To quotient.Count
        quotient.Cells(i).Value = numerator.Cells(i).Value / denominator.Cells(i).Value
    Next i
End Sub

gives you

aft

Update

Not adjacent Cells solution

assuming your sheet

before

using the below code

Sub DivRanges()
    Dim quotient As Range, numerator As Range, denominator As Range

    Set numerator = Range("A1, C1, D1, E1")
    Set denominator = Range("A2, C2, D2, E2")
    Set quotient = Range("A3, C3, D3, E3")
    
    Dim c As Range
    Dim i As Long
    i = 0
    For Each c In quotient
        Range(c.Address) = Range(getNum(numerator.Address, i)) / Range(getNum(denominator.Address, i))
        i = i + 1
    Next
    
End Sub

Function getNum(str As String, i As Long) As String
    getNum = Split(str, ",")(i)
End Function

result is

result

Dividing not adjacent cells using VBA

1 Answers

Not adjacent Cells solution