| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get the name of a cell
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Get the name of a cell
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) "Alvin Hansen" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Get the name of a cell
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: 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) "Alvin Hansen" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Get the name of a cell
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) "Alvin Hansen" wrote in message ... 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: 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) "Alvin Hansen" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Get the name of a cell
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: 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) "Alvin Hansen" wrote in message ... 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: 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) "Alvin Hansen" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Get the name of a cell
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) "Alvin Hansen" wrote in message ... 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: 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) "Alvin Hansen" wrote in message ... 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: 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) "Alvin Hansen" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Get the name of a cell
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: 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) "Alvin Hansen" wrote in message ... 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: 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) "Alvin Hansen" wrote in message ... 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: 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) "Alvin Hansen" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Get the name of a cell
Hi Alvin,
If the cell with the start cell is in B1 then use =OFFSET(INDIRECT("A"&kursister!B1),0,0,COUNTA(kurs ister!$A:$A),1) if B1 contains just the row number, or =OFFSET(INDIRECT(kursister!B1),0,0,COUNTA(kursiste r!$A:$A),1) if B1 has the whole cell (such as A3) -- HTH RP (remove nothere from the email address if mailing direct) "Alvin Hansen" wrote in message ... 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: 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) "Alvin Hansen" wrote in message ... 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: 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) "Alvin Hansen" wrote in message ... 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: 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) "Alvin Hansen" wrote in message ... 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Get the name of a cell
Thanks again bob for the help
regards alvin "Bob Phillips" skrev: Hi Alvin, If the cell with the start cell is in B1 then use =OFFSET(INDIRECT("A"&kursister!B1),0,0,COUNTA(kurs ister!$A:$A),1) if B1 contains just the row number, or =OFFSET(INDIRECT(kursister!B1),0,0,COUNTA(kursiste r!$A:$A),1) if B1 has the whole cell (such as A3) -- HTH RP (remove nothere from the email address if mailing direct) "Alvin Hansen" wrote in message ... 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: 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) "Alvin Hansen" wrote in message ... 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: 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) "Alvin Hansen" wrote in message ... 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: 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) "Alvin Hansen" wrote in message ... 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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Get the name of a cell
Just noticed, I used English, I assume you translated to Danish?
Bob "Alvin Hansen" wrote in message ... Thanks again bob for the help regards alvin "Bob Phillips" skrev: Hi Alvin, If the cell with the start cell is in B1 then use =OFFSET(INDIRECT("A"&kursister!B1),0,0,COUNTA(kurs ister!$A:$A),1) if B1 contains just the row number, or =OFFSET(INDIRECT(kursister!B1),0,0,COUNTA(kursiste r!$A:$A),1) if B1 has the whole cell (such as A3) -- HTH RP (remove nothere from the email address if mailing direct) "Alvin Hansen" wrote in message ... 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: 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) "Alvin Hansen" wrote in message ... 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: 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) "Alvin Hansen" wrote in message ... 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: 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) "Alvin Hansen" wrote in message ... 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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Get the name of a cell
Yes i have a Macro there translated alle code to Danish
Alvin "Bob Phillips" skrev: Just noticed, I used English, I assume you translated to Danish? Bob "Alvin Hansen" wrote in message ... Thanks again bob for the help regards alvin "Bob Phillips" skrev: Hi Alvin, If the cell with the start cell is in B1 then use =OFFSET(INDIRECT("A"&kursister!B1),0,0,COUNTA(kurs ister!$A:$A),1) if B1 contains just the row number, or =OFFSET(INDIRECT(kursister!B1),0,0,COUNTA(kursiste r!$A:$A),1) if B1 has the whole cell (such as A3) -- HTH RP (remove nothere from the email address if mailing direct) "Alvin Hansen" wrote in message ... 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: 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) "Alvin Hansen" wrote in message ... 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: 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) "Alvin Hansen" wrote in message ... 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: 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) "Alvin Hansen" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Code to copy the formulae of one cell to all the cell in the rangewith the specific cell and columnnumber changing Options | Excel Discussion (Misc queries) | |||
| Code to copy the formulae of one cell to all the cell in the rangewith the specific cell and columnnumber changing | Excel Discussion (Misc queries) | |||
| Populate a cell if values in cell 1 and cell 2 match cell 3 and 4 | Excel Worksheet Functions | |||
| How to create/run "cell A equals Cell B put Cell C info in Cell D | Excel Discussion (Misc queries) | |||
| data validation to restrict input in cell based on value of cell above that cell | Excel Programming | |||
![Alvin Hansen[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
