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
--
Tyrell Fickenscher
Plant Manager / Agronomist

Here is at least one problem:
Set curcell = ActiveSheet.Cells(Counter, 46)
If curcell = Range("aa20") Then...
I assume AA20 is the cell containing the company you are trying to match.

curcell is a Range variable. When you use If curcell = Range("aa20") the
answer will be False because the two Ranges are not the same. It is true
that in certain situations where your intent is clear VBA will interpret
curcell to mean "the value in curcell" but the actual strict interpretation
is that curcell is a specific range in a specific location (having a specific
value). So when you compare it to another Range, it will see if the two
ranges are in every way equivalent, meaning they refer to the same address.

Instead, to explicitly tell VBA you only want to check if the values are
equal, write it this way:
If curcell.Value = Range("aa20").Value

--
- K Dales


"Tyrell" wrote:

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
--
Tyrell Fickenscher
Plant Manager / Agronomist