title: Match cell value to a combobox row value
body: <p>I'm trying to figure out a different method of running a piece of code.</p>
  
  <p>Basically what my code is doing at the moment is, looping though column Q in the Global sheet, then looping though Combobox2, when it finds a match the entire rows get moved to the sheet reference in column 1 of the combobox.</p>
  
  <p>Is it possible to use the Match function to achieve the same results and speed up the code??</p>
  
  <p>This is currently the code I'm using, it does what I need it to do, but I cannot get error handling working for it. And it there are many rows of data to loop through it can take a long time!</p>
  
  <p>Option 1:</p>
  
  <pre><code>Private Sub CommandButton6_Click()
  Dim i As Long, j As Long, lastG As Long, strWS As String, rngCPY As Range
  
  Dim StartTime As Double
  Dim SecondsElapsed As Double
  
  With Application
      .ScreenUpdating = False
      .EnableEvents = False
      .CutCopyMode = False
  End With
  
  StartTime = Timer
  
  If Range("L9") = "" Then
      MsgBox "You can't Split the Jobs at this stage. " &amp; vbLf &amp; vbLf &amp; "Please create the form for the Sub-Contractor First." &amp; vbLf &amp; vbLf &amp; "Please press Display Utilities to create form.", vbExclamation, "Invalid Operation"
      Exit Sub
  End If
  
  If sheets("Global").Range("A3") = "" Then
      MsgBox "The appears to be no application loaded." &amp; vbLf &amp; vbLf &amp; "Please load" &amp; " " &amp; Range("C11") &amp; " " &amp; "App and Planet Info, then click button 2 and try again.", vbExclamation, "Invalid Operation"
      Exit Sub
  End If
  
      On Error GoTo bm_Close_Out
  
  ' find last row
  lastG = sheets("Global").Cells(Rows.Count, "Q").End(xlUp).row
  
  If InStr(1, sheets("Payment Form").Range("A20").value, "THE VAT SHOWN IS YOUR OUTPUT TAX DUE TO CUSTOMS AND EXCISE") &gt; 0 Then
      If sheets("PAYMENT FORM").Range("L40") &gt;= 1 Then
          MsgBox "It appears you have already split the jobs, this operation can only be performed once.", vbExclamation, "Invalid Operation"
          Exit Sub
      Else
          For j = 0 To Me.ComboBox2.ListCount - 1
                  currval = Me.ComboBox2.List(j, 0) ' value to match
              For i = 3 To lastG
                  lookupVal = sheets("Global").Cells(i, "Q") ' value to find
                  If lookupVal = currval Then
                      Set rngCPY = sheets("Global").Cells(i, "Q").EntireRow
                      strWS = Me.ComboBox2.List(j, 1)
                      On Error GoTo bm_Need_Worksheet  '&lt;~~ if the worksheet in the next line does not exist, go make one
                      With Worksheets(strWS)
                          rngCPY.Copy
                          .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Insert shift:=xlDown
                      End With
                  End If
              Next i
          Next j
      End If
  Else
      If sheets("PAYMENT FORM").Range("L35") &gt;= 1 Then
          MsgBox "It appears you have already split the jobs, this operation can only be performed once.", vbExclamation, "Invalid Operation"
          Exit Sub
      Else
          For j = 0 To Me.ComboBox2.ListCount - 1
                  currval = Me.ComboBox2.List(j, 0) ' value to match
              For i = 3 To lastG
                  lookupVal = sheets("Global").Cells(i, "Q") ' value to find
                  If lookupVal = currval Then
                      Set rngCPY = sheets("Global").Cells(i, "Q").EntireRow
                      strWS = Me.ComboBox2.List(j, 1)
                      On Error GoTo bm_Need_Worksheet  '&lt;~~ if the worksheet in the next line does not exist, go make one
                      With Worksheets(strWS)
                          rngCPY.Copy
                          .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Insert shift:=xlDown
                      End With
                  End If
              Next i
          Next j
      End If
  End If
  
  GoTo bm_Close_Out
  
  bm_Need_Worksheet:
      On Error GoTo 0
      With Worksheet
      Dim wb As Workbook: Set wb = ThisWorkbook
      Dim wsTemplate As Worksheet: Set wsTemplate = wb.sheets("Template")
      Dim wsPayment As Worksheet: Set wsPayment = wb.sheets("Payment Form")
      Dim wsNew As Worksheet
      Dim lastRow2 As Long
      Dim Contract As String: Contract = sheets("Payment Form").Range("C9").value
      Dim SpacePos As Integer: SpacePos = InStr(Contract, "- ")
      Dim Name As String: Name = Left(Contract, SpacePos)
      Dim Name2 As String: Name2 = Right(Contract, Len(Contract) - Len(Name))
  
      Dim NewName As String: NewName = strWS
      Dim CCName As Variant: CCName = Me.ComboBox2.List(j, 2)
  
      Dim LastRow As Long: LastRow = wsPayment.Range("U36:U53").End(xlDown).row
  
  If InStr(1, sheets("Payment Form").Range("A20").value, "THE VAT SHOWN IS YOUR OUTPUT TAX DUE TO CUSTOMS AND EXCISE") &gt; 0 Then
      lastRow2 = wsPayment.Range("A23:A39").End(xlDown).row
  Else
      lastRow2 = wsPayment.Range("A18:A34").End(xlDown).row
  End If
  
      wsTemplate.Visible = True
      wsTemplate.Copy before:=sheets("Details"): Set wsNew = ActiveSheet
      wsTemplate.Visible = False
  
  If InStr(1, sheets("Payment Form").Range("A20").value, "THE VAT SHOWN IS YOUR OUTPUT TAX DUE TO CUSTOMS AND EXCISE") &gt; 0 Then
      With wsPayment
          For Each cell In .Range("A23:A39")
              If Len(cell) = 0 Then
                  If sheets("Payment Form").Range("C9").value = "Network" Then
                      cell.value = NewName &amp; " - " &amp; Name2 &amp; ": " &amp; CCName
                  Else
                      cell.value = NewName &amp; " -" &amp; Name2 &amp; ": " &amp; CCName
                  End If
                  Exit For
              End If
          Next cell
      End With
  Else
      With wsPayment
          For Each cell In .Range("A18:A34")
              If Len(cell) = 0 Then
                  If sheets("Payment Form").Range("C9").value = "Network" Then
                      cell.value = NewName &amp; " - " &amp; Name2 &amp; ": " &amp; CCName
                  Else
                      cell.value = NewName &amp; " -" &amp; Name2 &amp; ": " &amp; CCName
                  End If
                  Exit For
              End If
          Next cell
      End With
  End If
  
  If InStr(1, sheets("Payment Form").Range("A20").value, "THE VAT SHOWN IS YOUR OUTPUT TAX DUE TO CUSTOMS AND EXCISE") &gt; 0 Then
      With wsNew
          .Name = NewName
          .Range("D4").value = wsPayment.Range("A23:A39").End(xlDown).value
          .Range("D6").value = wsPayment.Range("L11").value
          .Range("D8").value = wsPayment.Range("C9").value
          .Range("D10").value = wsPayment.Range("C11").value
      End With
  Else
      With wsNew
          .Name = NewName
          .Range("D4").value = wsPayment.Range("A18:A34").End(xlDown).value
          .Range("D6").value = wsPayment.Range("L11").value
          .Range("D8").value = wsPayment.Range("C9").value
          .Range("D10").value = wsPayment.Range("C11").value
      End With
  End If
  
  wsPayment.Activate
  
      With wsPayment
          .Range("J" &amp; lastRow2 + 1).value = 0
          .Range("L" &amp; lastRow2 + 1).Formula = "=N" &amp; lastRow2 + 1 &amp; "-J" &amp; lastRow2 + 1 &amp; ""
          .Range("N" &amp; lastRow2 + 1).Formula = "='" &amp; NewName &amp; "'!L20"
          .Range("U" &amp; LastRow + 1).value = NewName &amp; ": "
          .Range("V" &amp; LastRow + 1).Formula = "='" &amp; NewName &amp; "'!I21"
          .Range("W" &amp; LastRow + 1).Formula = "='" &amp; NewName &amp; "'!I23"
          .Range("X" &amp; LastRow + 1).Formula = "='" &amp; NewName &amp; "'!K21"
      End With
      End With
  
      On Error GoTo bm_Close_Out
      Resume
  
  bm_Close_Out:
  
  
    SecondsElapsed = Round(Timer - StartTime, 2)
    MsgBox "This code ran successfully in " &amp; SecondsElapsed &amp; " seconds", vbInformation
  
  With Application
      .ScreenUpdating = True
      .EnableEvents = True
      .CutCopyMode = True
  End With
  
  End Sub
  </code></pre>
  
  <p>Option 2:</p>
  
  <pre><code>Private Sub CommandButton1_Click()
  Dim j As Long, strWS As String, rngCPY As Range, FirstAddress As String, sSheetsWithData As String
  Dim sSheetsWithoutData As String, lSheetRowsCopied As Long, lAllRowsCopied As Long, bFound As Boolean, sOutput As String
  
  Dim StartTime As Double
  Dim SecondsElapsed As Double
  
  With Application
      .ScreenUpdating = False
      .EnableEvents = False
      .CutCopyMode = False
      .EnableEvents = False
  End With
  
  StartTime = Timer
  
  On Error GoTo bm_Close_Out
  
  For j = 0 To UserForm2.ComboBox2.ListCount - 1
          bFound = False
          currval = UserForm2.ComboBox2.List(j, 0) ' value to match
         With sheets("Global")
              Set rngCPY = sheets("Global").Range("Q:Q").Find(currval, LookIn:=xlValues)
              If Not rngCPY Is Nothing Then
              bFound = True
                  lSheetRowsCopied = 0
                  FirstAddress = rngCPY.Address
                  Do
                      lSheetRowsCopied = lSheetRowsCopied + 1
                      strWS = UserForm2.ComboBox2.List(j, 1)
                      On Error GoTo bm_Need_Worksheet
                      With Worksheets(strWS)
                          rngCPY.EntireRow.Copy
                          .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Insert shift:=xlDown
                      End With
                      Set rngCPY = sheets("Global").Range("Q:Q").FindNext(rngCPY)
                  Loop Until rngCPY Is Nothing Or rngCPY.Address = FirstAddress
              Else
                  bFound = False
              End If
              If bFound Then
                  sSheetsWithData = sSheetsWithData &amp; "    " &amp; strWS &amp; " (" &amp; lSheetRowsCopied &amp; ")" &amp; vbLf
                  lAllRowsCopied = lAllRowsCopied + lSheetRowsCopied
              End If
          End With
  Next j
  
  bm_Need_Worksheet:
      Dim wb As Workbook: Set wb = ThisWorkbook
      Dim wsTemplate As Worksheet: Set wsTemplate = wb.sheets("Template")
      Dim wsPayment As Worksheet: Set wsPayment = wb.sheets("Payment Form")
      Dim wsNew As Worksheet
      Dim lastRow2 As Long
      Dim Contract As String: Contract = sheets("Payment Form").Range("C9").value
      Dim SpacePos As Integer: SpacePos = InStr(Contract, "- ")
      Dim Name As String: Name = Left(Contract, SpacePos)
      Dim Name2 As String: Name2 = Right(Contract, Len(Contract) - Len(Name))
  
      Dim NewName As String: NewName = strWS
      Dim CCName As Variant: CCName = UserForm2.ComboBox2.List(j, 2)
  
      Dim LastRow As Long: LastRow = wsPayment.Range("U36:U53").End(xlDown).row
  
  If InStr(1, sheets("Payment Form").Range("A20").value, "THE VAT SHOWN IS YOUR OUTPUT TAX DUE TO CUSTOMS AND EXCISE") &gt; 0 Then
      lastRow2 = wsPayment.Range("A23:A39").End(xlDown).row
  Else
      lastRow2 = wsPayment.Range("A18:A34").End(xlDown).row
  End If
  
      wsTemplate.Visible = True
      wsTemplate.Copy before:=sheets("Details"): Set wsNew = ActiveSheet
      wsTemplate.Visible = False
  
  If InStr(1, sheets("Payment Form").Range("A20").value, "THE VAT SHOWN IS YOUR OUTPUT TAX DUE TO CUSTOMS AND EXCISE") &gt; 0 Then
      With wsPayment
          For Each cell In .Range("A23:A39")
              If Len(cell) = 0 Then
                  If sheets("Payment Form").Range("C9").value = "Network" Then
                      cell.value = NewName &amp; " - " &amp; Name2 &amp; ": " &amp; CCName
                  Else
                      cell.value = NewName &amp; " -" &amp; Name2 &amp; ": " &amp; CCName
                  End If
                  Exit For
              End If
          Next cell
      End With
  Else
      With wsPayment
          For Each cell In .Range("A18:A34")
              If Len(cell) = 0 Then
                  If sheets("Payment Form").Range("C9").value = "Network" Then
                      cell.value = NewName &amp; " - " &amp; Name2 &amp; ": " &amp; CCName
                  Else
                      cell.value = NewName &amp; " -" &amp; Name2 &amp; ": " &amp; CCName
                  End If
                  Exit For
              End If
          Next cell
      End With
  End If
  
  If InStr(1, sheets("Payment Form").Range("A20").value, "THE VAT SHOWN IS YOUR OUTPUT TAX DUE TO CUSTOMS AND EXCISE") &gt; 0 Then
      With wsNew
          .Name = NewName
          .Range("D4").value = wsPayment.Range("A23:A39").End(xlDown).value
          .Range("D6").value = wsPayment.Range("L11").value
          .Range("D8").value = wsPayment.Range("C9").value
          .Range("D10").value = wsPayment.Range("C11").value
      End With
  Else
      With wsNew
          .Name = NewName
          .Range("D4").value = wsPayment.Range("A18:A34").End(xlDown).value
          .Range("D6").value = wsPayment.Range("L11").value
          .Range("D8").value = wsPayment.Range("C9").value
          .Range("D10").value = wsPayment.Range("C11").value
      End With
  End If
  
  wsPayment.Activate
  
      With wsPayment
          .Range("J" &amp; lastRow2 + 1).value = 0
          .Range("L" &amp; lastRow2 + 1).Formula = "=N" &amp; lastRow2 + 1 &amp; "-J" &amp; lastRow2 + 1 &amp; ""
          .Range("N" &amp; lastRow2 + 1).Formula = "='" &amp; NewName &amp; "'!L20"
          .Range("U" &amp; LastRow + 1).value = NewName &amp; ": "
          .Range("V" &amp; LastRow + 1).Formula = "='" &amp; NewName &amp; "'!I21"
          .Range("W" &amp; LastRow + 1).Formula = "='" &amp; NewName &amp; "'!I23"
          .Range("X" &amp; LastRow + 1).Formula = "='" &amp; NewName &amp; "'!K21"
      End With
  
      On Error GoTo bm_Close_Out
      Resume
  
  bm_Close_Out:
      If sSheetsWithData &lt;&gt; vbNullString Then
          sOutput = "# of rows copied to sheets:" &amp; vbLf &amp; vbLf &amp; sSheetsWithData &amp; vbLf &amp; _
              "Total rows copied = " &amp; lAllRowsCopied &amp; vbLf &amp; vbLf
      Else
          sOutput = "No sheets contained data to be copied" &amp; vbLf &amp; vbLf
      End If
  
      If sSheetsWithoutData &lt;&gt; vbNullString Then
          sOutput = sOutput &amp; "Sheets with no rows copied:" &amp; vbLf &amp; vbLf &amp; sSheetsWithoutData
      Else
          sOutput = sOutput &amp; "All sheets had data that was copied."
      End If
  
      If sOutput &lt;&gt; vbNullString Then MsgBox sOutput, , "Copy Report"
  
      Set rngCPY = Nothing
  
  SecondsElapsed = Round(Timer - StartTime, 2)
  MsgBox "This code ran successfully in " &amp; SecondsElapsed &amp; " seconds", vbInformation
  
  With Application
      .ScreenUpdating = True
      .EnableEvents = True
      .CutCopyMode = True
      .EnableEvents = True
  End With
  
  End Sub
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/je3iI.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/je3iI.png" alt="enter image description here"></a></p>