Option Explicit
Private m_rngCBSource1 As Excel.Range
Private Sub CommandButton1_Click()
Dim rngArea As Excel.Range
Dim rngCell As Excel.Range
Dim i As Long
If ComboBox1.ListIndex = -1 Then Exit Sub
i = ComboBox1.ListIndex + 1
For Each rngArea In m_rngCBSource1.Areas
If i > rngArea.Cells.Count Then
i = i - rngArea.Cells.Count
Else
Set rngCell = rngArea.Cells(i)
Exit For
End If
Next
If Not rngCell Is Nothing Then
rngCell(ColumnIndex:="C").Value = TextBox1.Text
End If
End Sub
Private Sub UserForm_Initialize()
Set m_rngCBSource1 = Worksheets("Substrate").Range("A1:A2,A4:A6,A8").Cells
Dim rngCell As Excel.Range
For Each rngCell In m_rngCBSource1
ComboBox1.AddItem rngCell.Text
Next
End Sub