Index Command In VBA

[MikeG]

I have a range in my worksheet called DMM_Range which is 1 column wide by 50 rows. In VBA, I would like to refer to each row in the range in turn in a loop.

I tried the code below, but the "Index" command is not acceptable to VBA. Can anyone help?

Thanks,

MikeG


For d = 1 To t

Cur_DMM = Index(Range("DMM_Range"), d, 1)

'Do stuff based on Cur_DMM

Next d

 

[shg]

Hi, Mike,


Index is accessible in VBA as WorksheetFunction.Index, but you don't need it here.

    For d = 1 To t
        Cur_DMM = Range("DMM_Range")(d, 1)
    Next d

 

[MikeG]

Hi, Mike,


Index is accessible in VBA as WorksheetFunction.Index, but you don't need it here.

    For d = 1 To t
        Cur_DMM = Range("DMM_Range")(d, 1)
    Next d

Perfect!

Thanks shg

Mike

 

[shg]

You're welcome.

Don't forget to declare your variables ...

 

[MikeG]

Sorry - one other thing.

Range("DMM_Range") has text values in (peoples's names with spaces) and is located in another worksheet.

When I read a value form this range into Cur_DMM I get "Empty" (although oddly enough, the first time I tested the macro, it did have the right name in it).

Do I need to have a Dim statement for Cur_DMM at the top of the macro?

Thanks,

MikeG

 

[shg]

Add this line to the code:

Application.GoTo Range("DMM_Range")

Does that select the correct range?

 

[MikeG]

Add this line to the code:

Application.GoTo Range("DMM_Range")

Does that select the correct range?

It does - and now the text variable reads OK. Thanks, not sure if it was something else I did or if the new line did it.

 

[shg]

You can remove the line -- it was just for testing.