Arrays/Ranges

[theeeeebenedikt]

Hi All,

I'm looking to find out if/how i can create a VBA array using ranges i have already set using Dim **** As Range, multiplying the cells down the range.

the 'base' formula i want to use is: =(( (value in range 1)/3600)*(value in range 2))/(value in range 3*1000)

where ranges 1, 2 and 3 are ranges i have just set, as above.

hopefully that makes a bit of sense...

cheers,

Ben

 

[RickXL]

One way would be to use the Offset property of the Range. For example:

Sub my_offset()
    For i = 0 To 9
        With Sheet2
            .Range("A1").offset(i, 0) = .Range("B10").offset(i, 0) / 3600 * .Range("D97").offset(i, 0) * .Range("Z136").offset(i, 0) * 1000
        End With
    Next
End Sub

 

[Teeroy]

Assuming that all Ranges are the same size, and named Range1, Range2 and Range3, you could use;

For i = 1 To Range1.Rows.Count
    Range1.Cells(i, 1).Value = (Range1.Cells(i, 1).Value / 3600) * (Range2.Cells(i, 1).Value / (Range3.Cells(i, 1).Value * 1000))
Next i

Note that since the Sheet isn't specifically referenced it works on the ActiveSheet.

 

[theeeeebenedikt]

bang on Teeroy, had to tweak my pre-conditioning ranges a bit but that was the missing link pal cheers!!

ps up the mighty maroons

 

[Teeroy]

ps up the mighty maroons

Booooooooooo

Happy to help.