PDA

View Full Version : Find the first cell in a column by criteria and replace with combobox value



Loss1003
10-21-2016, 02:00 PM
How can I find the first cell in a column by criteria and then replace that with a userform combobox value.

For instance, let’s say I have a column (D) with the following formula dragged down to each cell.
=IF(C2="MP","Mike P","")

And I wanted the first cell that meets the following criteria: “” in that column to be replaced with a userform combo box value (Player2) after the command button is clicked on.

See attached worksheet.

mana
10-21-2016, 04:13 PM
Dim r As Range, c As Range

Set r = Sheets("sheet1").Range("a1").CurrentRegion.Columns("d").Cells

For Each c In r
If c.Value = "" Then
c.Value = ComboBox1.Value
End If
Next

Loss1003
10-25-2016, 08:19 AM
Thanks for the code. How can I adjust it to fill in only the first ("") cell located. Not all of the cells ("").

The user clicks on the submit button and the first cell that equals ("") should be filled with the combobox only. If the user clicks on the submit button again it would drop down to the next cell that equals ("") and so on.

mana
10-26-2016, 07:04 AM
Insert this.

Exit for
or
Exit sub