Problem with finding two values.

Mike Neal

New Member
Joined
May 24, 2020
Messages
38
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,623
Office Version
  1. 2007
Platform
  1. Windows
Hi and welcome to MrExcel.
Try this

To clear:
VBA Code:
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:
VBA Code:
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

New Member
Joined
May 24, 2020
Messages
38
Office Version
  1. 2013
Platform
  1. Windows
Hi and welcome to MrExcel.
Try this

To clear:
VBA Code:
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:
VBA Code:
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

New Member
Joined
May 24, 2020
Messages
38
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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.
 

Mike Neal

New Member
Joined
May 24, 2020
Messages
38
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Dec 3, 2018
Messages
12,623
Office Version
  1. 2007
Platform
  1. Windows
populate combox1 with those values

I show you 2 ways:

Rich (BB code):
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:
Rich (BB code):
    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

New Member
Joined
May 24, 2020
Messages
38
Office Version
  1. 2013
Platform
  1. Windows
I show you 2 ways:

Rich (BB code):
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:
Rich (BB code):
    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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,417
Messages
5,642,003
Members
417,250
Latest member
spr1nger

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top