PDA

View Full Version : Help with "Array fuctioons"



Demise
12-31-2013, 02:32 PM
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
12-31-2013, 04:45 PM
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
01-01-2014, 05:18 AM
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
01-01-2014, 05:21 AM
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
01-01-2014, 06:21 AM
@XLD --



The way I read it, it should be


Agree :doh:

You read it better

Paul

PS -- Welcome back -- we missed you

Bob Phillips
01-01-2014, 06:47 AM
PS -- Welcome back -- we missed you

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