Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Number to letter in Row Source
Having a little trouble with this and can't seem to find it in Google.
The following works for ComboBox "TeamPick" TeamPick.RowSource = "Divisions!B4:B11" Problem is that I want to use an integer variable in place of the column letter. e.g. Dim i as Integer i = 5 TeamPick.RowSource should be range E4:E11 I know I could jury-rig this with a lookup table somewhere but was hoping there was a simpler solution. Thanks, John |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Number to letter in Row Source
try Dim i as Integer i= 5 with worksheets("Divisions") set TeamPick.RowSource = range(.cells(4,i),.cells(11,i)) msgbox range(.cells(4,i),.cells(11,i)).address ' please delete end with Note this is untested. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Number to letter in Row Source
Kieran,
Didn't work, but thanks anyway. I'll play with it some more tomorrow. John "Kieran" wrote in message ... try Dim i as Integer i= 5 with worksheets("Divisions") set TeamPick.RowSource = range(.cells(4,i),.cells(11,i)) msgbox range(.cells(4,i),.cells(11,i)).address ' please delete end with Note this is untested. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Number to letter in Row Source
John,
you can set RowSource dynamically, not only in design mode So, for the comboBox Initialize event (or activate or enter - you should choose proper event to undate the combo box list - let me know if don't know how), use this code: Dim FirstRow as integer Dim LastRow as integer Dim Col as integer Dim ListSource as range ' point to the range you want, you can change that dynamically instead of fixed numbers FirstRow = 4 LastRow=11 Col=5 set ListSource = Sheets("Divisions").Range(cells(FirstRow, Col), cells(LastRow, Col)) TeamPick.RowSource = ListSource It's untested, but should work RADO "John Wilson" wrote in message ... Having a little trouble with this and can't seem to find it in Google. The following works for ComboBox "TeamPick" TeamPick.RowSource = "Divisions!B4:B11" Problem is that I want to use an integer variable in place of the column letter. e.g. Dim i as Integer i = 5 TeamPick.RowSource should be range E4:E11 I know I could jury-rig this with a lookup table somewhere but was hoping there was a simpler solution. Thanks, John |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Number to letter in Row Source
How about:
Dim i As Integer Dim strCol As String i = 5 strCol = Chr(i + 64) TeamPick.RowSource = strCol & "4:" & strCol & "11" -- HTH, Dianne In , John Wilson typed: Having a little trouble with this and can't seem to find it in Google. The following works for ComboBox "TeamPick" TeamPick.RowSource = "Divisions!B4:B11" Problem is that I want to use an integer variable in place of the column letter. e.g. Dim i as Integer i = 5 TeamPick.RowSource should be range E4:E11 I know I could jury-rig this with a lookup table somewhere but was hoping there was a simpler solution. Thanks, John |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Number to letter in Row Source
Dim i as Integer
i = 5 TeamPick.RowSource=Range("A4:A11").Offset(0,i-1).Address(External:=True) from the immediate window: i = 5 ? Range("A4:A11").Offset(0,i-1).Address(External:=True) [Book1]Sheet1!$E$4:$E$11 -- Regards, Tom Ogilvy "John Wilson" wrote in message ... Kieran, Didn't work, but thanks anyway. I'll play with it some more tomorrow. John "Kieran" wrote in message ... try Dim i as Integer i= 5 with worksheets("Divisions") set TeamPick.RowSource = range(.cells(4,i),.cells(11,i)) msgbox range(.cells(4,i),.cells(11,i)).address ' please delete end with Note this is untested. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Number to letter in Row Source
set ListSource = Sheets("Divisions").Range(cells(FirstRow, Col),
cells(LastRow, Col)) Assumes the activesheet for the Cells objects. Either preface all with a sheet reference or don't preface any and assume the activesheet. Also, Rowsource is expecting a string, so you would need to do TeamPick.RowSource = ListSource.Address and probably better would be TeamPick.RowSource = ListSource.Address(external:=True) -- Regards, Tom Ogilvy "RADO" wrote in message ... John, you can set RowSource dynamically, not only in design mode So, for the comboBox Initialize event (or activate or enter - you should choose proper event to undate the combo box list - let me know if don't know how), use this code: Dim FirstRow as integer Dim LastRow as integer Dim Col as integer Dim ListSource as range ' point to the range you want, you can change that dynamically instead of fixed numbers FirstRow = 4 LastRow=11 Col=5 set ListSource = Sheets("Divisions").Range(cells(FirstRow, Col), cells(LastRow, Col)) TeamPick.RowSource = ListSource It's untested, but should work RADO "John Wilson" wrote in message ... Having a little trouble with this and can't seem to find it in Google. The following works for ComboBox "TeamPick" TeamPick.RowSource = "Divisions!B4:B11" Problem is that I want to use an integer variable in place of the column letter. e.g. Dim i as Integer i = 5 TeamPick.RowSource should be range E4:E11 I know I could jury-rig this with a lookup table somewhere but was hoping there was a simpler solution. Thanks, John |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Number to letter in Row Source
While it may not be an issue here, if
strCol = left(columns(i).Address(0,0), 2 + (i < 27)) it could handle an i value greater than 26. -- Regards, Tom Ogilvy "Dianne" wrote in message ... How about: Dim i As Integer Dim strCol As String i = 5 strCol = Chr(i + 64) TeamPick.RowSource = strCol & "4:" & strCol & "11" -- HTH, Dianne In , John Wilson typed: Having a little trouble with this and can't seem to find it in Google. The following works for ComboBox "TeamPick" TeamPick.RowSource = "Divisions!B4:B11" Problem is that I want to use an integer variable in place of the column letter. e.g. Dim i as Integer i = 5 TeamPick.RowSource should be range E4:E11 I know I could jury-rig this with a lookup table somewhere but was hoping there was a simpler solution. Thanks, John |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Number to letter in Row Source
Nice one -- filed away.
-- Dianne In , Tom Ogilvy typed: While it may not be an issue here, if strCol = left(columns(i).Address(0,0), 2 + (i < 27)) it could handle an i value greater than 26. "Dianne" wrote in message ... How about: Dim i As Integer Dim strCol As String i = 5 strCol = Chr(i + 64) TeamPick.RowSource = strCol & "4:" & strCol & "11" -- HTH, Dianne In , John Wilson typed: Having a little trouble with this and can't seem to find it in Google. The following works for ComboBox "TeamPick" TeamPick.RowSource = "Divisions!B4:B11" Problem is that I want to use an integer variable in place of the column letter. e.g. Dim i as Integer i = 5 TeamPick.RowSource should be range E4:E11 I know I could jury-rig this with a lookup table somewhere but was hoping there was a simpler solution. Thanks, John |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Number to letter in Row Source
The property row source accepts only strings not ranges, that´s the
reason why fails... so, what you should use is simply: TeamPick.RowSource = range(.cells(4,i),.cells(11,i)).address Mika. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Number to letter in Row Source
Tom, et.al.,
My thanks to you and everyone else that responded. I finally got a chance to try some of these suggestions out. What I finally used was: TeamPick.RowSource = _ Worksheets("Divisions"). _ Range("A19:A26").offset(0, DivPicked - 1). _ Address(External:=True) John "Tom Ogilvy" wrote in message ... Dim i as Integer i = 5 TeamPick.RowSource=Range("A4:A11").Offset(0,i-1).Address(External:=True) from the immediate window: i = 5 ? Range("A4:A11").Offset(0,i-1).Address(External:=True) [Book1]Sheet1!$E$4:$E$11 -- Regards, Tom Ogilvy "John Wilson" wrote in message ... Kieran, Didn't work, but thanks anyway. I'll play with it some more tomorrow. John "Kieran" wrote in message ... try Dim i as Integer i= 5 with worksheets("Divisions") set TeamPick.RowSource = range(.cells(4,i),.cells(11,i)) msgbox range(.cells(4,i),.cells(11,i)).address ' please delete end with Note this is untested. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I turn excel columns from column number to column letter? | Setting up and Configuration of Excel | |||
how do I change column headers from a number to a letter | Excel Discussion (Misc queries) | |||
Column() to return a letter instead of a number? | Excel Worksheet Functions | |||
column header changed from letter to number, how return to letter | Excel Discussion (Misc queries) | |||
Auto number w/ different letter-number combos in same column | Excel Worksheet Functions |