Columns Letters to Number keep changing

[ipbr21054]

Hi,

Sometimes after i press a command button i see a RTE
Clicking debug shows me the line of code that is the cause.

What ive noticed is the code shows example Range("P7").Select in yellow.
The issue is because for some unknown reason the columns are now shown as 1,2,3 etc as opposed to A,B,C

Changing the R1C1 option in the settings does fix this BUT why does it keep changing from numbers to letters on its own every now & then ?

Thanks

 

[jasonb75]

It wouldn't change on its own unless your code is telling it to somewhere. Look for the line

Application.ReferencStyle = xlR1C1

in your code.

Most likely it is being changed and for some reason not being changed back, possibly due to a procedure being ended early and skipping over the relevant line.

 

[ipbr21054]

Im sure actually positive that line of code isnt in use.

BUT is there a line of code i can use to check my whole workbook as looking for that on each worksheet is going to take a while

 

[jasonb75]

is there a line of code i can use to check my whole workbook

Not a line of code, but a quick way. Open the code editor, then open any module in the relevant workbook. Press Ctrl f to open the Find dialog box, type ReferencStyle into the search box, change the option / radio button to 'Current Project' then click Find Next.

Bearing in mind that it is a change made at application level, it could potentially be in a different workbook that you had open when the problem occurred, not the one where you are seeing the runtime error.

 

[ipbr21054]

Do i need to do this for every module as there are a few of them ?
I assume yes so will need to check them all.

Thanks

 

[jasonb75]

As far as I can see, changing the radio button to 'Current Project' as I suggested should search every module in the project (workbook).

It's not something I've ever tried doing so I don't know for certain, perhaps test it with something that you know is in more than one module and see if it is found?

 

[ipbr21054]

Hi,
It returns search text is not found

 

[jasonb75]

As I said originally, it might be in a different workbook, in your personal.xlsb if you have one, perhaps an add in.
In short, there is no reason for the reference type to change unless someone is either doing it manually or it is hidden away in some code somewhere.
If your excel installation was corrupt to the point that it was happening for no reason then you would be experiencing much bigger problems.

 

[ipbr21054]

Ok,
Ive just been through all the excel sheets & found 1 which had numbers,so ive changed to letters.

I will see what happens now.

Thanks

 

[RoryA]

FYI, the fact that your Excel GUI is showing column numbers would not prevent a reference like Range("P7") from working.