Naming cells in Excel

[darbid]

I have tried to get an excel forum to help here but they got shy when the vba code was in Access and not in the excel worksheet.

with this code below I am defining a cell in excel

objShtdata.Names.Add Name:="" & strcellname & "", RefersToR1C1:="='CP Tables'!R" & iRowc & "C1"

Then with this i go back to it to enter things later on

objXL.Goto objShtdata.Range("" & strcellname & "").Select

This works fine from my computer but on others it does not goto the named cell.

The problem that I can see is after the "add Name" has run there is a difference in my excel sheet and the one on other computer. The difference is this
Mine the "refers to:" from insert>name>define

='CP Tables'!$A$1

On other computers it looks like this

='CP Tables'!R1C1

Which does not work.

So I am guessing there is something wrong with this

objShtdata.Names.Add Name:="" & strcellname & "", RefersToR1C1:="='CP Tables'!R" & iRowc & "C1"

What do people think?

 

[chergh]

What does objShtdata refer to?

When I add named ranges I typically use

WorkbookName.Names.Add

rather than

WorksheetName.Names.Add

I think I started doing this because I had issues with getting the named ranges working as I expected with the worksheet.

Also instead of:

objXL.Goto objShtdata.Range("" & strcellname & "").Select

this

objXL.Goto strcellname

should work fine.

 

[darbid]

What does objShtdata refer to?

It refers to the worksheet.

So I have changed to your code as follows

objWkb.Names.Add Name:="" & strcellname & "", RefersToR1C1:="='CP Tables'!R" & iRowc & "C1"

and

objXL.Goto strcellname

This works on my computer fine. If I look in the excel file on my computer insert>name>define they are properly referred to as

='CP Tables'!$C$5

But when this code runs on another computer and I look at the same name that was created from the code it is

='CP Tables'!'R5C3'

 

[chergh]

On the computer you are having the problem with goto Tools>Options>General Tab and make sure R1C1 reference style is not checked.

 

[darbid]

On the computer you are having the problem with goto Tools>Options>General Tab and make sure R1C1 reference style is not checked.

No it is not checked on the "other computers"
I was thinking that it is a problem with how I name the range so I just recorded a simple macro on a "other computer" of defining a range.

ActiveWorkbook.Names.Add Name:="hello", RefersToR1C1:="=Table1!R22C3"

which looks ok.

 

[chergh]

I really have no idea why the reference is showing up in r1c1 notation on the different computers.

I use the following sub to add my named ranges when I need them:

Sub CreateNamedRange(NameRng As String, ws As Worksheet)

Dim lastRow As Long
Dim lastCol As Long

lastRow = ws.Range("A65536").End(xlUp).Row
lastCol = ws.Range("IV1").End(xlToLeft).Column

Workbooks("OTD3.xls").Names.Add Name:=NameRng, RefersToR1C1:="=" & ws.Name & "!R1C1:R" & CStr(lastRow) & "C" & CStr(lastCol)


End Sub

Instead of using 'RefersToR1C1' you could use 'RefersTo' along with the address property of the range object and see if that makes a difference.

You could also try removing the single quotes from your worksheet name as that is the only difference I can see between other code and yours, I wouldn't expect it to make any difference though as it currently works on yours.

 

[darbid]

I really have no idea why the reference is showing up in r1c1 notation on the different computers.

These computers should be identical as they all have exactly the same windows and office versions. The only difference is that they all have Microsoft Language Packs and mine is in English and so far I have only tested this ie "the other computers" on computers speaking german. But I cannot for the life of me imagine that makes a difference.

You could also try removing the single quotes from your worksheet name as that is the only difference I can see between other code and yours, I wouldn't expect it to make any difference though as it currently works on yours.

I will try that but I think the single quotes are there cause the name has a space in it.

And chergh thanks heaps for helping and not giving up.

 

[chergh]

In fact instead of hardcoding the name of the sheet use the name property of the sheet:

objWkb.Names.Add Name:= strcellname , RefersToR1C1:="=" & objShtdata.name & "!R" & iRowc & "C1"

 

[darbid]

It is a dam language problem and the first I have come accross.


This works for excel speaking english

objWkb.Names.Add Name:="" & strcellname & "", RefersToR1C1:="='CP Tables'!R" & iRowc & "C1"

this works for excel speaking german

objWkb.Names.Add Name:="" & strcellname & "", RefersToR1C1:="='CP Tables'!Z" & iRowc & "S1"

Z being Zeile or Row
S being Spalte or Column

Sooooooo now how to fix this. The only way I can think is to check what language either office or windows is in. and then use the appropriate reference.