Problem with finding two values.

[Mike Neal]

I am new to VBA and am trying to write a macro that will search for a value that is in a UserForm text box which will always be in row 1 on my excel sheet and then search the column that the value is found in for a value from a ComboBox in the same Userform. After finding the value from the ComboBox I want to clear it from the cell by clicking a command button. I also have a second ComboBox that I would like to use to add a value to the next empty cell in the column found from the same text box by clicking a different command button. The text box is a machine ID number and the the first ComboBox has a list of maintenance tasks assigned to that machine which I would like to be able to select one and remove . The second ComboBox has a list of all the possible maintenance tasks that could be assigned to the machine and I would like to able to select one and add it. I have been able to use Find to get the first value but have not been able to use select that column and search it for the second value. Any help would be appreciated. Thank you.

 

[DanteAmor]

Hi and welcome to MrExcel.
Try this

To clear:

Private Sub CommandButton1_Click()
' to clear
  If TextBox1.Value = "" Then
    MsgBox "Enter ID number"
    TextBox1.SetFocus
    Exit Sub
  End If
  If ComboBox1.ListIndex = -1 Then
    MsgBox "Select task"
    ComboBox1.SetFocus
    Exit Sub
  End If
 
  Dim f As Range
  Set f = Rows(1).Find(TextBox1.Value, , xlValues, xlWhole)
  If Not f Is Nothing Then
    Set f = Columns(f.Column).Find(ComboBox1.Value, , xlValues, xlWhole)
    If Not f Is Nothing Then
      f.Delete Shift:=xlUp
      MsgBox "Task deleted"
    Else
      MsgBox "Task does not exists"
    End If
  Else
    MsgBox "ID does not exists"
  End If
End Sub

To add:

Private Sub CommandButton2_Click()
' to add
  If TextBox1.Value = "" Then
    MsgBox "Enter ID number"
    TextBox1.SetFocus
    Exit Sub
  End If
  If ComboBox2.ListIndex = -1 Then
    MsgBox "Select task"
    ComboBox2.SetFocus
    Exit Sub
  End If
 
  Dim f As Range, lr As Long, col As Long
  Set f = Rows(1).Find(TextBox1.Value, , xlValues, xlWhole)
  If Not f Is Nothing Then
    col = f.Column
    Set f = Columns(col).Find(ComboBox2.Value, , xlValues, xlWhole)
    If Not f Is Nothing Then
      MsgBox "The task already exists"
      ComboBox2.SetFocus
    Else
      lr = Cells(Rows.Count, col).End(3).Row + 1
      Cells(lr, col).Value = ComboBox2.Value
    End If
  Else
    MsgBox "ID does not exists"
  End If
End Sub

 

[Mike Neal]

I will give it a try. Thank you.

 

[Mike Neal]

Hi and welcome to MrExcel.
Try this

To clear:

Private Sub CommandButton1_Click()
' to clear
  If TextBox1.Value = "" Then
    MsgBox "Enter ID number"
    TextBox1.SetFocus
    Exit Sub
  End If
  If ComboBox1.ListIndex = -1 Then
    MsgBox "Select task"
    ComboBox1.SetFocus
    Exit Sub
  End If

  Dim f As Range
  Set f = Rows(1).Find(TextBox1.Value, , xlValues, xlWhole)
  If Not f Is Nothing Then
    Set f = Columns(f.Column).Find(ComboBox1.Value, , xlValues, xlWhole)
    If Not f Is Nothing Then
      f.Delete Shift:=xlUp
      MsgBox "Task deleted"
    Else
      MsgBox "Task does not exists"
    End If
  Else
    MsgBox "ID does not exists"
  End If
End Sub

To add:

Private Sub CommandButton2_Click()
' to add
  If TextBox1.Value = "" Then
    MsgBox "Enter ID number"
    TextBox1.SetFocus
    Exit Sub
  End If
  If ComboBox2.ListIndex = -1 Then
    MsgBox "Select task"
    ComboBox2.SetFocus
    Exit Sub
  End If

  Dim f As Range, lr As Long, col As Long
  Set f = Rows(1).Find(TextBox1.Value, , xlValues, xlWhole)
  If Not f Is Nothing Then
    col = f.Column
    Set f = Columns(col).Find(ComboBox2.Value, , xlValues, xlWhole)
    If Not f Is Nothing Then
      MsgBox "The task already exists"
      ComboBox2.SetFocus
    Else
      lr = Cells(Rows.Count, col).End(3).Row + 1
      Cells(lr, col).Value = ComboBox2.Value
    End If
  Else
    MsgBox "ID does not exists"
  End If
End Sub

Everything works good. The only problem I am having now is that I have some values in the column where we are adding that is below where I would like the new values to go. Is there a way to insert the values in rows 10 thru 19 but still use the first empty cell of that range? Thank you

 

[Mike Neal]

Everything works good. The only problem I am having now is that I have some values in the column where we are adding that is below where I would like the new values to go. Is there a way to insert the values in rows 10 thru 19 but still use the first empty cell of that range? Thank you

I solved this myself by changing the how I stored the data on the sheet. Thanks.

 

[DanteAmor]

Glad to hear it works for you now.

 

[Mike Neal]

Dante I have been trying to reuse the code "to add" to look at rows 9 thru 19 in the column that has the value of textbox1 and populate combox1 with those values. I figured from the line col = f.Column I should be able to define a range (row 9 thru 19) and set those values as combobox1.list. Everything I try seems to throw an error code or just doesn't put the values there. Can you give me any assistance with this? Thank you.

 

[DanteAmor]

populate combox1 with those values

I show you 2 ways:

Private Sub CommandButton3_Click()
  If TextBox1.Value = "" Then
    MsgBox "Enter ID number"
    TextBox1.SetFocus
    Exit Sub
  End If
  
  Dim f As Range, lr As Long, col As Long
  Set f = Rows(1).Find(TextBox1.Value, , xlValues, xlWhole)
  If Not f Is Nothing Then
    ComboBox1.List = Range(Cells(9, f.Column), Cells(19, f.Column)).Value
  Else
    MsgBox "ID does not exists"
  End If
End Sub

Or:

    ComboBox1.List = f.Offset(8).Resize(11).Value

Personally I prefer the first one, as it shows me which row to which row I'm loading.

 

[Mike Neal]

I show you 2 ways:

Private Sub CommandButton3_Click()
  If TextBox1.Value = "" Then
    MsgBox "Enter ID number"
    TextBox1.SetFocus
    Exit Sub
  End If
 
  Dim f As Range, lr As Long, col As Long
  Set f = Rows(1).Find(TextBox1.Value, , xlValues, xlWhole)
  If Not f Is Nothing Then
    ComboBox1.List = Range(Cells(9, f.Column), Cells(19, f.Column)).Value
  Else
    MsgBox "ID does not exists"
  End If
End Sub

Or:

    ComboBox1.List = f.Offset(8).Resize(11).Value

Personally I prefer the first one, as it shows me which row to which row I'm loading.

The list is empty even though I have data in the cell. I think it is because the Private Sub is a Click. I was hoping for it to run as soon as the ID was typed into the textbox. Do I need to add a command button for it to work? Thank you.

 

[DanteAmor]

Do I need to add a command button for it to work?

That's right, you have to put a button, press the button and voila, you will have the load in the combo.