Add ranges to match and index functions

Darren Smith

Well-known Member
Joined
Nov 23, 2020
Messages
536
Office Version
  1. 2019
Platform
  1. Windows
Also using case function for the ranges.
Please help to join the Match, Index results to specified Ranges


VBA Code:
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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Darren Smith

Well-known Member
Joined
Nov 23, 2020
Messages
536
Office Version
  1. 2019
Platform
  1. Windows
Also asked Stackover flow
 

Darren Smith

Well-known Member
Joined
Nov 23, 2020
Messages
536
Office Version
  1. 2019
Platform
  1. Windows
Hi All

Can someone help today with the above, please
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,980
Office Version
  1. 365
Platform
  1. Windows
Please supply links to all other sites where you have asked this question. Thanks
 

Darren Smith

Well-known Member
Joined
Nov 23, 2020
Messages
536
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,136,445
Messages
5,675,900
Members
419,591
Latest member
mersanko

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top