Problem with Cells and Offset

[pragmath]

My question is quite simple but it drives me nuts!
Cell AN10, i.e. row 10 and column 40 contains the value 8. I am reading it into the integer Time using the VBA code

Time = Cells(4,3).Offset(6,37)

However the result is Time = 3, wich happens to be the value in cell AO10, i.e. cell(10,41). I have tried

Time = Cells(4,col)Offset(6,40 - col)

for values of col between 1 and 40 and I get the correct value 8 except for col = 3 and col = 38.
Is it a VBA bug or am I missing something very, very basic?

Thanks for any an help.

 

[Andrew Poulsom]

Welcome to MrExcel.

Do you have any cells that are merged horizontally?

 

[Peter_SSs]

Welcome to the MrExcel board!

The basic structure look okay to me but Time is a vba Keyword (refer vba Help Time function and Time statement) so I would advise against using it as a variable name .

 

[Darren Bartrup]

Hi and welcome to the board.

I tried your code and it works fine:

Sub test()

    Dim lTime As Long


    lTime = Sheet1.Cells(4, 3).Offset(6, 37)
    
End Sub

The problems I can see are:

• You're using Time as a variable as it's a reserved word in VBA - it returns the system time and when I tried it I got a data type mismatch.
• Your code doesn't explicitly state the sheet it's looking at - are you sure it's looking at the right sheet?

In the Immediate window this returns $AN$10:

?thisworkbook.worksheets("Sheet1").Cells(4, 3).Offset(6, 37).address

 

[Andrew Poulsom]

If C4:D4 are merged this returns AO10:

MsgBox Cells(4, 3).Offset(6, 37).Address

 

[pragmath]

That's exactly the problem, and likewise for cells AL4:AM4 that are merged. Merging cells is very dangerous, but sometimes this is needed to have nice headings.

Thanks an awful lot.

 

[bschwartz]

For the record, you can often avoid merging cells, and still accomplish the same aesthetic effects, but using Center across Selection located on the Alignment tab of the Format Cells dialog box, under Horizontal.

 

[pragmath]

I use the Center across selection option in VBA but I didn't know it is available in Excel. I will now use it systematically.
Thanks for the info

 

[bschwartz]

You're welcome.