|
List Fill Code
I am using the following code to fill the list in a combobox. As you can see
the values are assigned to cells. The cell reference to B54 is the line after the code. EXCEL gets extremely slow when assigning the values. I am wondering if it will speed up tremendously if I code the entire operation rather than using the cell values. If so how do I store each additional value that will be added to the list programmatically. The output should be fairly straightforward. Sub ModelListFill() Dim ModelNumber As Integer Dim i As Integer ModelNumber = 0 For i = 4 To 100 If Sheets("Defaults").Range("B" & i) < "" Then Sheets("Defaults").Range("X" & i).Value = Sheets("Defaults").Range("B" & i).Value ModelNumber = ModelNumber + 1 Else i = 100 End If Next Sheets("Variables").Range("B54").Value = ModelNumber Sheet1.ComboBox6.ListFillRange = Sheets("Variables").Range("B55").Value End Sub ="Defaults!X4:X"&B54+3 Thanks for any help |
List Fill Code
Cody,
Try the code below. If column B has blank cells below a certain point, than copying the entire range over to column X should work. And the countif should give you the list range. If column B has some entries, than some blanks, and than some entries - you will have to use a loop. But I think this will work (?).... (code untested) ==================== Sub ModelListFill() Dim ModelNumber As Integer Dim i As Integer Dim cel As Range Dim rng As Range Sheets("Defaults").Range("X4:X100").Value = Sheets("Defaults").Range("B4:B100").Value ModelNumber = WorksheetFunction.CountA(Sheets("Defaults").Range( "B4:B100")) + 3 Set rng = Sheets("Defaults").Range("X4:X" & ModelNumber) Sheet1.ComboBox6.ListFillRange = rng End Sub ================================== -- steveB Remove "AYN" from email to respond "Cody" wrote in message ... I am using the following code to fill the list in a combobox. As you can see the values are assigned to cells. The cell reference to B54 is the line after the code. EXCEL gets extremely slow when assigning the values. I am wondering if it will speed up tremendously if I code the entire operation rather than using the cell values. If so how do I store each additional value that will be added to the list programmatically. The output should be fairly straightforward. Sub ModelListFill() Dim ModelNumber As Integer Dim i As Integer ModelNumber = 0 For i = 4 To 100 If Sheets("Defaults").Range("B" & i) < "" Then Sheets("Defaults").Range("X" & i).Value = Sheets("Defaults").Range("B" & i).Value ModelNumber = ModelNumber + 1 Else i = 100 End If Next Sheets("Variables").Range("B54").Value = ModelNumber Sheet1.ComboBox6.ListFillRange = Sheets("Variables").Range("B55").Value End Sub ="Defaults!X4:X"&B54+3 Thanks for any help |
List Fill Code
Steve,
The main problem I am trying to get away from is assigning the values to another column to enter in the ListFillRange property. I would like to figure out how to decide which values need to be applied to ListFillRange and do so with a program rather than filling a reference range of cells. "STEVE BELL" wrote: Cody, Try the code below. If column B has blank cells below a certain point, than copying the entire range over to column X should work. And the countif should give you the list range. If column B has some entries, than some blanks, and than some entries - you will have to use a loop. But I think this will work (?).... (code untested) ==================== Sub ModelListFill() Dim ModelNumber As Integer Dim i As Integer Dim cel As Range Dim rng As Range Sheets("Defaults").Range("X4:X100").Value = Sheets("Defaults").Range("B4:B100").Value ModelNumber = WorksheetFunction.CountA(Sheets("Defaults").Range( "B4:B100")) + 3 Set rng = Sheets("Defaults").Range("X4:X" & ModelNumber) Sheet1.ComboBox6.ListFillRange = rng End Sub ================================== -- steveB Remove "AYN" from email to respond "Cody" wrote in message ... I am using the following code to fill the list in a combobox. As you can see the values are assigned to cells. The cell reference to B54 is the line after the code. EXCEL gets extremely slow when assigning the values. I am wondering if it will speed up tremendously if I code the entire operation rather than using the cell values. If so how do I store each additional value that will be added to the list programmatically. The output should be fairly straightforward. Sub ModelListFill() Dim ModelNumber As Integer Dim i As Integer ModelNumber = 0 For i = 4 To 100 If Sheets("Defaults").Range("B" & i) < "" Then Sheets("Defaults").Range("X" & i).Value = Sheets("Defaults").Range("B" & i).Value ModelNumber = ModelNumber + 1 Else i = 100 End If Next Sheets("Variables").Range("B54").Value = ModelNumber Sheet1.ComboBox6.ListFillRange = Sheets("Variables").Range("B55").Value End Sub ="Defaults!X4:X"&B54+3 Thanks for any help |
List Fill Code
Cody,
My bad... from your code I thought that is what you wanted. But to just review the cells and add to the combobox without assigning ranges.... Dim cel As Range Sheets("sheet1").ComboBox1.Clear For Each cel In Sheets("Sheet1").Range("B4:B100") If Len(cel) 0 Then Sheets("sheet1").ComboBox1.AddItem cel.Text End If Next -- steveB Remove "AYN" from email to respond "Cody" wrote in message ... Steve, The main problem I am trying to get away from is assigning the values to another column to enter in the ListFillRange property. I would like to figure out how to decide which values need to be applied to ListFillRange and do so with a program rather than filling a reference range of cells. "STEVE BELL" wrote: Cody, Try the code below. If column B has blank cells below a certain point, than copying the entire range over to column X should work. And the countif should give you the list range. If column B has some entries, than some blanks, and than some entries - you will have to use a loop. But I think this will work (?).... (code untested) ==================== Sub ModelListFill() Dim ModelNumber As Integer Dim i As Integer Dim cel As Range Dim rng As Range Sheets("Defaults").Range("X4:X100").Value = Sheets("Defaults").Range("B4:B100").Value ModelNumber = WorksheetFunction.CountA(Sheets("Defaults").Range( "B4:B100")) + 3 Set rng = Sheets("Defaults").Range("X4:X" & ModelNumber) Sheet1.ComboBox6.ListFillRange = rng End Sub ================================== -- steveB Remove "AYN" from email to respond "Cody" wrote in message ... I am using the following code to fill the list in a combobox. As you can see the values are assigned to cells. The cell reference to B54 is the line after the code. EXCEL gets extremely slow when assigning the values. I am wondering if it will speed up tremendously if I code the entire operation rather than using the cell values. If so how do I store each additional value that will be added to the list programmatically. The output should be fairly straightforward. Sub ModelListFill() Dim ModelNumber As Integer Dim i As Integer ModelNumber = 0 For i = 4 To 100 If Sheets("Defaults").Range("B" & i) < "" Then Sheets("Defaults").Range("X" & i).Value = Sheets("Defaults").Range("B" & i).Value ModelNumber = ModelNumber + 1 Else i = 100 End If Next Sheets("Variables").Range("B54").Value = ModelNumber Sheet1.ComboBox6.ListFillRange = Sheets("Variables").Range("B55").Value End Sub ="Defaults!X4:X"&B54+3 Thanks for any help |
List Fill Code
Steve,
That is exactly what I wanted, thank you. I am not sure what the "Len(cel)" is but HELP should help me out. Thanks again. Cody "STEVE BELL" wrote: Cody, My bad... from your code I thought that is what you wanted. But to just review the cells and add to the combobox without assigning ranges.... Dim cel As Range Sheets("sheet1").ComboBox1.Clear For Each cel In Sheets("Sheet1").Range("B4:B100") If Len(cel) 0 Then Sheets("sheet1").ComboBox1.AddItem cel.Text End If Next -- steveB Remove "AYN" from email to respond "Cody" wrote in message ... Steve, The main problem I am trying to get away from is assigning the values to another column to enter in the ListFillRange property. I would like to figure out how to decide which values need to be applied to ListFillRange and do so with a program rather than filling a reference range of cells. "STEVE BELL" wrote: Cody, Try the code below. If column B has blank cells below a certain point, than copying the entire range over to column X should work. And the countif should give you the list range. If column B has some entries, than some blanks, and than some entries - you will have to use a loop. But I think this will work (?).... (code untested) ==================== Sub ModelListFill() Dim ModelNumber As Integer Dim i As Integer Dim cel As Range Dim rng As Range Sheets("Defaults").Range("X4:X100").Value = Sheets("Defaults").Range("B4:B100").Value ModelNumber = WorksheetFunction.CountA(Sheets("Defaults").Range( "B4:B100")) + 3 Set rng = Sheets("Defaults").Range("X4:X" & ModelNumber) Sheet1.ComboBox6.ListFillRange = rng End Sub ================================== -- steveB Remove "AYN" from email to respond "Cody" wrote in message ... I am using the following code to fill the list in a combobox. As you can see the values are assigned to cells. The cell reference to B54 is the line after the code. EXCEL gets extremely slow when assigning the values. I am wondering if it will speed up tremendously if I code the entire operation rather than using the cell values. If so how do I store each additional value that will be added to the list programmatically. The output should be fairly straightforward. Sub ModelListFill() Dim ModelNumber As Integer Dim i As Integer ModelNumber = 0 For i = 4 To 100 If Sheets("Defaults").Range("B" & i) < "" Then Sheets("Defaults").Range("X" & i).Value = Sheets("Defaults").Range("B" & i).Value ModelNumber = ModelNumber + 1 Else i = 100 End If Next Sheets("Variables").Range("B54").Value = ModelNumber Sheet1.ComboBox6.ListFillRange = Sheets("Variables").Range("B55").Value End Sub ="Defaults!X4:X"&B54+3 Thanks for any help |
List Fill Code
Cody,
Glad it works. Len(cel) is asking for the length or # of characters in the cel if the cel is blank than this should = 0 if there is anything in the cel that it will be greater than 0 Using this prevents empty cells from being added to the combobox. -- steveB Remove "AYN" from email to respond "Cody" wrote in message ... Steve, That is exactly what I wanted, thank you. I am not sure what the "Len(cel)" is but HELP should help me out. Thanks again. Cody "STEVE BELL" wrote: Cody, My bad... from your code I thought that is what you wanted. But to just review the cells and add to the combobox without assigning ranges.... Dim cel As Range Sheets("sheet1").ComboBox1.Clear For Each cel In Sheets("Sheet1").Range("B4:B100") If Len(cel) 0 Then Sheets("sheet1").ComboBox1.AddItem cel.Text End If Next -- steveB Remove "AYN" from email to respond "Cody" wrote in message ... Steve, The main problem I am trying to get away from is assigning the values to another column to enter in the ListFillRange property. I would like to figure out how to decide which values need to be applied to ListFillRange and do so with a program rather than filling a reference range of cells. "STEVE BELL" wrote: Cody, Try the code below. If column B has blank cells below a certain point, than copying the entire range over to column X should work. And the countif should give you the list range. If column B has some entries, than some blanks, and than some entries - you will have to use a loop. But I think this will work (?).... (code untested) ==================== Sub ModelListFill() Dim ModelNumber As Integer Dim i As Integer Dim cel As Range Dim rng As Range Sheets("Defaults").Range("X4:X100").Value = Sheets("Defaults").Range("B4:B100").Value ModelNumber = WorksheetFunction.CountA(Sheets("Defaults").Range( "B4:B100")) + 3 Set rng = Sheets("Defaults").Range("X4:X" & ModelNumber) Sheet1.ComboBox6.ListFillRange = rng End Sub ================================== -- steveB Remove "AYN" from email to respond "Cody" wrote in message ... I am using the following code to fill the list in a combobox. As you can see the values are assigned to cells. The cell reference to B54 is the line after the code. EXCEL gets extremely slow when assigning the values. I am wondering if it will speed up tremendously if I code the entire operation rather than using the cell values. If so how do I store each additional value that will be added to the list programmatically. The output should be fairly straightforward. Sub ModelListFill() Dim ModelNumber As Integer Dim i As Integer ModelNumber = 0 For i = 4 To 100 If Sheets("Defaults").Range("B" & i) < "" Then Sheets("Defaults").Range("X" & i).Value = Sheets("Defaults").Range("B" & i).Value ModelNumber = ModelNumber + 1 Else i = 100 End If Next Sheets("Variables").Range("B54").Value = ModelNumber Sheet1.ComboBox6.ListFillRange = Sheets("Variables").Range("B55").Value End Sub ="Defaults!X4:X"&B54+3 Thanks for any help |
| All times are GMT +1. The time now is 04:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com