Help with "Array fuctioons"

[Demise]

Start comment

Hi, I've bee trying to do the following:

Make it so that (pseudo code):
Cells c columns 5:140 = cells a 5:140 / cells b 5:140

But I haven't found a way to run a for loop through the cells to make this possible. I would put my attempts here but I haven't had much progress on it and including it wouldn't help.

End comment

[Paul_Hossler]

I think this is what you're looking for.



Option Explicit

Sub LoopDemo()
Dim rCell As Range

For Each rCell In ActiveSheet.Range("A5:A140").Cells
If rCell.Offset(0, 2).Value <> 0 Then rCell.Value = rCell.Offset(0, 1).Value / rCell.Offset(0, 2).Value
Next
End Sub



Paul

[Bob Phillips]

The way I read it, it should be


Sub LoopDemo()
Dim rCell As Range

For Each rCell In ActiveSheet.Range("A5:A140").Cells

With rCell

If .Offset(0, 1).Value <> 0 Then

.Offset(0, 2).Value = .Value / .Offset(0, 1).Value
End If
End With
Next
End Sub

[Bob Phillips]

Another way without looping


Sub NoLoopDemo()

With ActiveSheet.Range("C5:C140")

.Formula = "=IF(B5<>0,A5/B5,"""")"
.Value = .Value
End With
End Sub

[Paul_Hossler]

@XLD --



The way I read it, it should be


Agree :doh:

You read it better

Paul

PS -- Welcome back -- we missed you

[Bob Phillips]

PS -- Welcome back -- we missed you

Thank-you. I missed VBAX and you guys :)