Add ranges to match and index functions

[Darren Smith]

Also using case function for the ranges.
Please help to join the Match, Index results to specified Ranges

Private Sub Refresh_Drawing_Numbers_Click()


        Dim cmb As ComboBox
        Dim wsSource As Worksheet, wsDest As Worksheet
        Dim wsSourceLastRow As Long, wsDestLastRow As Long
        Dim IndexRng As Range, MatchRng As Range
        Dim i As Integer
        Dim RefreshDrNumbers As Long
        
        Set cmb = Me.Refresh_Drawing_Numbers
        Set wsSource = ThisWorkbook.Worksheets("Parts List")
        Set wsDest = ThisWorkbook.Worksheets("Job Card Master")
        
        
        wsSourceLastRow = wsSource.Range("A" & Rows.Count).End(xlUp).Row
        wsDestLastRow = wsDest.Range("A" & Rows.Count).End(xlUp).Row
        
      
           Set IndexRng = wsSource.Range("F1:F" & wsSourceLastRow)
           Set MatchRng = wsSource.Range("E1:E" & wsSourceLastRow)

            Select Case cmb.Value
    
          Case ("Refresh DrawingNo.s 1 Page Jobcard")
                        RefreshDrNumbers wsDest.Range("A13:Q" & wsDestLastRow)
            
          Case ("Refresh DrawingNo.s 2 Page Jobcard")
                        RefreshDrNumbers wsDest.Range("A13:Q61")
                        RefreshDrNumbers wsDest.Range("A66:Q" & wsDestLastRow)
                        
          Case ("Refresh DrawingNo.s 3 Page Jobcard")
                        RefreshDrNumbers wsDest.Range("A13:Q61")
                        RefreshDrNumbers wsDest.Range("A66:Q122")
                        RefreshDrNumbers wsDest.Range("A127:Q" & wsDestLastRow)
                        
           Case ("Refresh DrawingNo.s 4 Page Jobcard")
                        RefreshDrNumbers wsDest.Range("A13:Q61")
                        RefreshDrNumbers wsDest.Range("A66:Q122")
                        RefreshDrNumbers wsDest.Range("A127:Q183")
                        RefreshDrNumbers wsDest.Range("A188:Q" & wsDestLastRow)
                        
           Case ("Refresh DrawingN0.s 5 Page Jobcard")
                        RefreshDrNumbers wsDest.Range("A13:Q61")
                        RefreshDrNumbers wsDest.Range("A66:Q122")
                        RefreshDrNumbers wsDest.Range("A127:Q183")
                        RefreshDrNumbers wsDest.Range("A188:Q244")
                        RefreshDrNumbers wsDest.Range("A249:Q" & wsDestLastRow)
    End Select
        End Sub
       Sub RefreshDrNumbers()
       
       Dim i As Integer
       
        For i = 2 To wsDestLastRow
            Dim MatchRow
            MatchRow = Application.Match(wsDestDest.Range("E" & i).Value, MatchRng, 0)
            If IsError(MatchRow) Then
                wsDest.Range("B" & i).Value = ""
            Else
                wsDest.Range("B" & i).Value = IndexRng.Cells(MatchRow, 1)
                
                End If
                
                Next i
            
            End If
End Sub

 

[Darren Smith]

Also asked Stackover flow

Using VBA Match and Index function

Trying to use match & Index with specified ranges. Does not recognise RefreshDrNumbers in the code. I am using the Case Function to specify ranges. Can't seem to make the Case, Match & Index

stackoverflow.com

 

[Darren Smith]

Please can someone help

 

[Darren Smith]

Hi All

Can someone help today with the above, please

 

[Fluff]

Please supply links to all other sites where you have asked this question. Thanks

 

[Darren Smith]

Using VBA Match and Index function

Trying to use match & Index with specified ranges. Does not recognise RefreshDrNumbers in the code. I am using the Case Function to specify ranges. Can't seem to make the Case, Match & Index

stackoverflow.com

Using VBA Match and Index function - OzGrid Free Excel/VBA Help Forum

Mod note: Cross-posted here: Add ranges to match and index functions | MrExcel Message Board [URL:https://www.mrexcel.com/board/threads/add-ranges-to-match-and-index-functions.1162701/] Trying to use match & Index with specified ranges Match, Index &…

www.ozgrid.com

Please supply links to all other sites where you have asked this question. Thanks

Here you go.
I would really appreciate your advice on this question.