Struggling with VBA Offset!

[Thebatfink]

Ok, so I have this ..

For Each mycells In Sheet4.Range("o6:o68").Cells

....

Next

I also want to get the value of a cell offset from the current cell in the For / Next iteration. I use..

mycells.offset(0,-5).value

and it errors.. I thought mycells would hold the cell reference, but it seems to hold the cell value (hence my offset errors). So I know I can get the cell location using..

mycells.address

but I can't add .offset.value to the end of that because it errors.. So how do I actually get the value of the cell, 5 cells to the left of the current cell being used in the For / Next iteration!!

Thanks

 

[Gettingbetter]

Hello

lose the .cells bit of your for

<Code>
Dim mycells As Range
For Each mycells In Range("o668")
MsgBox mycells.Offset(0, -5)
Next
</Code>

Cheers
GB

 

[MickG]

Perhaps Try this:-

For Each Mycells In Sheets("Sheet4").Range("O6:O68")

NB:- sheet4.range("O6:O68") will return the Sheet with the codeName "Sheet4", not the sheet with the "tab" name "sheet4"

 

[Thebatfink]

I need to use the Sheet CodeName as the actual sheet name is variable.. Can you not use .range on the codename?

If I use

For Each Mycells In Sheet4.Range("o6:o68")

mycells.Offset(0, 5)

Errors.. Is it because of the codename use?

 

[Gettingbetter]

Hey

<Code>
Dim mycells As Range
For Each mycells In Sheet4.Range("o668")
MsgBox mycells.Offset(0, -5)
Next
</Code>

Works for me. Are you sure that the worksheet is called Sheet4 (If you look in vbaproject window of the VBE you can see then sheet names)

or try
MsgBox ActiveSheet.CodeName
to check

 

[AD_Taylor]

I need to use the Sheet CodeName as the actual sheet name is variable.. Can you not use .range on the codename?

If I use

For Each Mycells In Sheet4.Range("o6:o68")

mycells.Offset(0, 5)

Errors.. Is it because of the codename use?

No the codename use is fine.

Make sure you've got:

Dim myCells as Range

at the top of your code (or at least before you want to use it)
Then myCells.Offset should work - at least it does for me.

 

[MickG]

If you code name and sheet name are the same, I imagine you will get the same result.
For example my sheet name is "sheet49" and I run the code :- Sheet49.select , It opens "sheet41" because Sheet41's code name is Sheet49.
If I run your code as below the return value I get is from Column "J" in Sheet named "sheet41" .

For Each Mycells In Sheet49.Range("O6:O68")
MsgBox Mycells.Offset(, -5).Address' 'Returns same address for both sheets Names.
MsgBox Mycells.Offset(, -5).Value
Next

This returned values from Sheet Named "Sheet49")

For Each Mycells In Sheets("Sheet49").Range("O6:O68")

If Sheet49 (code name) does not exist you get an error.

 

[Thebatfink]

Thanks for the help guys. You are indeed correct, its my bad coding thats causing the errors! Sorry.

I am holding the value of the offset cell in a string variable and I hadn't accomodated a check in my code to deal with the chance of the offset cell being empty, and it just so happens the first row in my test spreadsheet has an empty offset cell!!

Thanks for all the help, its appreciated.

 

[SallyBlackerf]

Thanks for the help guys. You are indeed correct