Return Cell Location?

[Thebatfink]

Hi,

I'm looping through a range in my sub basically looking for cells with none blank values and then using this value in the sub. Heres the code (the sheet to loop is obviously selected from a combobox)..

For Each myNumber In Sheets("" & comboboxday.Value & "").Range("H6:H52")
blah blah
Next myNumber

Now I have a requirement to use another cells value part way through the sub. It is always 10 cells to the right of the cell/row of myNumber which it is currently processing. I was hoping to just use offset to get to the cell value, but obviously myNumber is a cell value, not a location.

Is there anyway I can get at myNumber's cell location?

Thanks!
Batfink

 

[Centrican]

Why not have mynumber be the location and use mynumber.value where you need to know the value of the cell?

 

[mikerickson]

You can use a range variable.

Dim oneCell as Range

For Each oneCell in Sheets("" & comboboxday.Value & "").Range("H6:H52").Cells
    With oneCell
        MsgBox "The value 10 columns right of " & .Address & " is " & .Offset(0, 10).Value

        Rem do stuff
    End With
Next oneCell

 

[Thebatfink]

Hi,

Thanks for the help. Worked great