G
Guest
In Column "AT" (46) I have seed companies listed starting in row 2 on down
with a count of the number of seed companies in row 1. In Column "AS" (45) I
have a count of the number of hybrids for each seed company with the hybrids
listed, next to the appropriate seed company, in Column "AU" (47) on to the
right.
What I want to do is be able to remove a hybrid selected by the user. What
I have is a combobox (cboSSeedCo2) with a list of the seed companies, once a
company is selected I want a second combobox (cboSHybrid) to populate with a
list of the hybrids for that company. The first set of code below is located
in the "Sheet Code" and is used to populate cboSHybrid, which I cannot get to
work properly. The second set of code is used to delete the selected hybrid
and shift all other hybrids that are to the right of the deleted hybrid to
the left...therefore creating a row (horizontal) list with no blank cells.
For whatever reason I cannot get this to work properly and the code below is
all I need to work. Are there any recommendations on how to fix the code
below to work that way I have outlined above? Any help would be greatly
appreciated!
Private Sub cboSSeedCo2_Change()
Dim i, iEnd, iHybrid As Integer
i = Range("at1")
For Counter = 1 To i + 1
Set curcell = ActiveSheet.Cells(Counter, 46)
If curcell = Range("aa20") Then
iHybrid = Cells(Counter, 45)
iEnd = iHybrid + 46
ActiveSheet.cboSHybrid.ListFillRange = Range(Cells(Counter, 47)
& ":" & Cells(Counter, iEnd))
End If
Next Counter
End Sub
Sub RemoveHybrid()
Dim i, j, val As Integer
i = Range("at1")
For Counter = 1 To i + 1
Set curcell = ActiveSheet.Cells(Counter, 46)
If curcell = Range("aa20") Then
val = Counter
End If
Next Counter
j = Range("as" & val)
For Counter = 47 To j + 46
Set curcell = ActiveSheet.Cells(val, 47)
If curcell = Range("aa21") Then
ActiveSheet.Cells(val, 47 + Range("as" & Counter)).Delete
Shift:=xlShiftLeft
Worksheets("Index").Range("au" & val & ":iv" & val).Sort
Key1:=Worksheets("Index").Range("au" & val)
ActiveSheet.Range("as" & val) = ActiveSheet.Range("as" & val) - 1
End If
Next Counter
End Sub
with a count of the number of seed companies in row 1. In Column "AS" (45) I
have a count of the number of hybrids for each seed company with the hybrids
listed, next to the appropriate seed company, in Column "AU" (47) on to the
right.
What I want to do is be able to remove a hybrid selected by the user. What
I have is a combobox (cboSSeedCo2) with a list of the seed companies, once a
company is selected I want a second combobox (cboSHybrid) to populate with a
list of the hybrids for that company. The first set of code below is located
in the "Sheet Code" and is used to populate cboSHybrid, which I cannot get to
work properly. The second set of code is used to delete the selected hybrid
and shift all other hybrids that are to the right of the deleted hybrid to
the left...therefore creating a row (horizontal) list with no blank cells.
For whatever reason I cannot get this to work properly and the code below is
all I need to work. Are there any recommendations on how to fix the code
below to work that way I have outlined above? Any help would be greatly
appreciated!
Private Sub cboSSeedCo2_Change()
Dim i, iEnd, iHybrid As Integer
i = Range("at1")
For Counter = 1 To i + 1
Set curcell = ActiveSheet.Cells(Counter, 46)
If curcell = Range("aa20") Then
iHybrid = Cells(Counter, 45)
iEnd = iHybrid + 46
ActiveSheet.cboSHybrid.ListFillRange = Range(Cells(Counter, 47)
& ":" & Cells(Counter, iEnd))
End If
Next Counter
End Sub
Sub RemoveHybrid()
Dim i, j, val As Integer
i = Range("at1")
For Counter = 1 To i + 1
Set curcell = ActiveSheet.Cells(Counter, 46)
If curcell = Range("aa20") Then
val = Counter
End If
Next Counter
j = Range("as" & val)
For Counter = 47 To j + 46
Set curcell = ActiveSheet.Cells(val, 47)
If curcell = Range("aa21") Then
ActiveSheet.Cells(val, 47 + Range("as" & Counter)).Delete
Shift:=xlShiftLeft
Worksheets("Index").Range("au" & val & ":iv" & val).Sort
Key1:=Worksheets("Index").Range("au" & val)
ActiveSheet.Range("as" & val) = ActiveSheet.Range("as" & val) - 1
End If
Next Counter
End Sub