Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Range.Offset produces incorrect cell address



In producing a fairly involved solution to this question Need Help Improving/increase Speed of ChangeTracking Macro, I noticed a strange problem (or made a mistake I can't see!).

In looping through two 2D arrays (and writing to a third) using counters m (effectively 1 to 25) and n (effectively 1 to 10) , I get a problem only when n is 4 (which is row 9  of the TDatas sheet in the file provided for that question). The code writes one of the values to the third array using the line:

ChngArr(r, 2) = .Cells(m, n).Offset(5, 1).Address

but with m= 4 and n=9 (say), it records $Z$9 (and the same for n values other than 1).

If however (with that sheet active) I check in the VB Project Explorer's Intermediate window, I get the correct result, J9 using:

Debug.Print Cells(4, 9).Offset(5, 1).Address

The same happens when m=4 (only) with three later lines like:

Select Case CurrArr(m, n)
                Case Is > PrevArr(m, n)
                    .Cells(m, n).Offset(5, 27).Interior.ColorIndex = 4 'green

but with the changed offset, it applies the colour to cell AB9 correctly (n=1) but to AZ9 (rather than cells AC9:AY9 successively when n>1).

Any ideas?


Answer the Question

You must create an account to use the forum. Create an Account or Login