VBA : return address of values found by countif

[JIB]

Hi everyone, Would appreciate it if I can get some help with this.

I'm basically doing a data entry form for guest registration for a dinner.
However, I have to ensure guest names cannot have duplicate entries, and if possible, guests are registered in the cell's next to the seat number that have already been entered, rather than adding new entries for seat and name everytime. The reason being, I'm linking it to another sheet which is the floor seating plan. Chairs turn blue when a guest's name is registered to the seat number

Would love some help to return the address found by countif (a) and have the guest's name (Combobox1.value) transferred to the cell next to it

Private Sub CommandButton1_Click()


Dim a As Long, x As Long
a = Application.WorksheetFunction.CountIf(Sheet3.Range("A:A"), Me.ComboBox2.Text)
'Checks for duplicate entries for the seat specified in combobox 2
x = Application.WorksheetFunction.CountA(Sheet3.Range("A:A")) + 1
'Total amount of cells + 1
C = Application.WorksheetFunction.CountIf(Sheet3.Range("B:B"), Me.ComboBox1.Text)
If Me.ComboBox1 <> "" And Me.ComboBox2 <> "" And a = 0 And C = 0 Then
Sheet3.Range("a" & x).Value = Me.ComboBox2.Text
Sheet3.Range("b" & x).Value = Me.ComboBox1.Text
Range("A2:A108").Select
ActiveWorkbook.Worksheets("A").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("A").Sort.SortFields.Add Key:=Range("A2"), SortOn _
:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("A").Sort
.SetRange Range("A2:B108")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

ElseIf Me.ComboBox1 <> "" And Me.ComboBox2 <> "" And C = 0 And a <> 0 Then
MsgBox "Seat is already registered. Registering guest"

'Find address of countif for seats and transfer me.combobox1.text to the cell next to it
'Loc = Sheet3.Cells("a").Address
'Sheet3.Range("loc").Value = Me.ComboBox1.Text

End If
If C <> 0 Then
MsgBox "Guest is already registered"
End If
End Sub

 

[DanteAmor]

Could be like this:

Private Sub CommandButton2_Click()
  Dim f As Range
  If ComboBox1.Value = "" Then
    MsgBox "Enter name"
    Exit Sub
  End If
  If ComboBox2.Value = "" Then
    MsgBox "Enter seat"
    Exit Sub
  End If
  
  Set f = Sheet3.Range("A:A").Find(ComboBox2, , xlValues, xlWhole)
  If Not f Is Nothing Then
    f.Offset(, 1).Value = ComboBox1.Value
  Else
    MsgBox "Not found"
  End If
End Sub

__________________________________________
But if you loaded the seats this way, then:

Private Sub UserForm_Activate()
  ComboBox2.List = Sheet3.Range("A2:A" & Sheet3.Range("A" & Rows.Count).End(xlUp).Row).Value
End Sub

Private Sub CommandButton1_Click()
  If ComboBox1.Value = "" Then
    MsgBox "Enter name"
    Exit Sub
  End If
  If ComboBox2.Value = "" Or ComboBox2.ListIndex = -1 Then
    MsgBox "Enter seat"
    Exit Sub
  End If
  Sheet3.Range("B" & ComboBox2.ListIndex + 2).Value = ComboBox1.Value
End Sub

 

[JIB]

The first VBA is exactly what I was looking for. Thank you so much!
I did try the second one but there was an issue with this line 'ComboBox2.List = Sheet3.Range("A2:A" & Sheet3.Range("A" & Rows.Count).End(xlUp).Row).Value'

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

Merry Christmas!

 

[JIB]

Set f = Sheet3.Range("A:A").Find(ComboBox2, , xlValues, xlWhole)
  If Not f Is Nothing Then
    f.Offset(, 1).Value = ComboBox1.Value
  Else
    MsgBox "Not found"
  End If
End Sub

Sorry, I just realized the call value is still being replaced despite the msgbox popping up. I tried

if Not f Is Nothing and c=0 Then

and it still replaces the cell value. Would love some feedback. Please and thank you

 

[DanteAmor]

I did not understand what you need now. I don't understand this either c = 0