Get the name of a cell

[Guest]

Hi
CAn i with this code also get the name of the cell
Set myrange2 =
Sheets(Range("kurt!$h$1").Value).Range(Range("kurt!$h$1").Value)

For Each b In myrange2
kursist1.AddItem b.Text

Next
If i just can get the cell name also then it is perfect

Regards alvin

 

[Bob Phillips]

What do you mean by name? If you want its address, use

b.Address

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Thanks bob
yes its was the address
but how can i get this adress
in my combobox
just now i have this
Range("kursist!$g$1").Value = kursist1.Text
but i also want the address into another cell
can i do that?
Like if i make my choise in the combobox i get
value kursist.text into one cell and the address into another cell

Reagrds
Alvin


"Bob Phillips" skrev:

 

[Bob Phillips]

Not really sure that I understand, but if your combobox is filled from a
worksheet range, when an item is selected from the combox, ListIndex returns
the index, so you could use that to build the address.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Thanks bob
I undrstand now
then there are just one thing before i can use it
i have a name range
=Offset(kursister!$A$1;0;0;TÆLV(kursister!$A:$A);1)
The TÆLV is because it is a danish excel
But hope you can understnd it anyway i ges yu know
Offset. Now her it is
instead of $A$1 then i want to have a cell value i try but get an error no
matter what i do

Hoope you can help

Regards
alvin

"Bob Phillips" skrev:

 

[Bob Phillips]

Alvin,

Do you want to set the range name from VBA? If so, assuming the listindex
value is in a variable idx say, you can do it with

ActiveWorkbook.Names.Add name:="your_name",
RefersTo:="=OFFSET(kursister!$A$" & idx & ",0,0,COUNTA(kursister!$A:$A),1)"

Note that I use the English functions, because as I understand it in VBA all
code is English, and Excel will automatically convert for you.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Hi bob
No i want to Set the range name ( or start) from a cell in a sheet
I have been tool yhat i can't use a variabel from vba into a sheet

Alvin



"Bob Phillips" skrev:

 

[Bob Phillips]

Hi Alvin,

If the cell with the start cell is in B1 then use

=OFFSET(INDIRECT("A"&kursister!B1),0,0,COUNTA(kursister!$A:$A),1)

if B1 contains just the row number, or

=OFFSET(INDIRECT(kursister!B1),0,0,COUNTA(kursister!$A:$A),1)

if B1 has the whole cell (such as A3)

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Yes i have a Macro there translated alle code to Danish

Alvin

"Bob Phillips" skrev: