Range passed to a function - trying to just move through the range

[John Black]

I know this is a simple thing but I can't make the syntax work. I am passed a range into a
function that is 3 columns wide and some variable number of rows long.

The For loop is just trying to run through each of the rows in the range and pull the values
from the 3 columns in each row into variables to do some calculations.

Function WAR(Sel As Range) As Double

Y = Sel.Rows.Count

For i = 0 To (Y - 1)
A = Sel.Offset(i, 0).Value
B = Sel.Offset(i, 1).Value
C = Sel.Offset(i, 2).Value
Use values...
Next i
....

But this is not working. Can I not use Offset(x,y) to move around within the range Sel? If
not, now do I reference values at a given x,y coordinate within the range? Thanks.

John Black

 

[Dave Unger]

John,
Change your code as shown, then you'll see why it's not working. You're not stepping thru each cell, you're selecting an offset to the whole range. Hope this helps,

regards,

Dave

Function WAR(Sel As Range) As Double
Y = Sel.Rows.Count

For i = 0 To (Y - 1)
Sel.Offset(i, 0).Select
Sel.Offset(i, 1).Select
Sel.Offset(i, 2).Select

' A = Sel.Offset(i, 0).Value
' B = Sel.Offset(i, 1).Value
' C = Sel.Offset(i, 2).Value
' Use values...

Next i
End Function

 

[Dave Unger]

Hi John,

Forgot to include a solution in previous post, this should do what you want.

regards,

Dave

Function WAR(Sel As Range) As Double

Y = Sel.Rows.Count

For i = 1 To Y
A = Sel(i, 1).Value
B = Sel(i, 2).Value
C = Sel(i, 3).Value

Next i
End Function

 

[John Black]

Hi John,

Forgot to include a solution in previous post, this should do what you want.

regards,

Dave

Function WAR(Sel As Range) As Double

Y = Sel.Rows.Count

For i = 1 To Y
A = Sel(i, 1).Value
B = Sel(i, 2).Value
C = Sel(i, 3).Value

Next i
End Function

Dave, thank you so much!! Ok, so I didn't need the offset. Sel(x,y) gets me around the
range. Too simple!

One question though, why is (1,1) the first cell in the range instead of (0,0)? Sometimes
things start at 0 and other times at 1?

John Black

 

[GS]

(e-mail address removed) says...

Dave, thank you so much!! Ok, so I didn't need the offset. Sel(x,y)
gets me around the range. Too simple!

One question though, why is (1,1) the first cell in the range instead
of (0,0)? Sometimes things start at 0 and other times at 1?

John Black

Ranges start at 1,1! (There is no Rows(0) or Columns(0) on a worksheet.
Thus, if you dump a range into an array...

Dim vRng As Variant
vRng = ActiveSheet.Range("A1:E5")

...you end up with a 2D array containing 5 rows and 5 cols. You loop
this like so...

Dim i&, n&
For i = LBound(vRng) To UBound(vRng) '//num rows (1 To 5)
For n = LBound(vRng, 2) To UBound(vRng, 2) '//num cols (1 To 5)
Debug.Print vRng(i, n)
Next 'n (col)
Next 'i (row)

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion