Finding Values on separate worksheet

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
419
Office Version
  1. 2019
Platform
  1. Windows
I need to find a drawing no in a separate sheet using index and match or Dictionary.
I have a Listbox with quite a few columns.
Columns 1,4,6, 2 of the Listbox need to match the columns and cells in the source worksheet then add the result in my main sheet.
At the moment It finds the value in column D but it needs to be dynamic and work off a selection of values from columns on my List Box.
Source range is from C2 to L & lasts row down

VBA Code:
[CODE=vba]Private Sub Reset_Drawing_Numbers_Click()
Dim matchRange As Range
Dim ODict As Object
Dim cmb As ComboBox
Dim SourceLastRow As Long, DestLastRow As Long
Dim wsSource As Worksheet, wsDest As Worksheet

Set wsSource = ThisWorkbook.Worksheets("Drawing No")
Set wsDest = ThisWorkbook.Worksheets("Job Card Master")
Set cmb = Me.Reset_Drawing_Numbers
SourceLastRow = wsSource.Range("A" & Rows.Count).End(xlUp).Row
DestLastRow = wsDest.Range("A" & Rows.Count).End(xlUp).Row
Set matchRange = wsSource.Range("C1:L" & SourceLastRow)
Set ODict = GetDictionary(matchRange, 1, 2)

Select Case cmb.Value
Dim DataRange As Range
Case ("Reset Drawing No. 1 Page")
Set DataRange = wsDest.Range("A13:Q" & DestLastRow)
Case ("Reset Drawing No. 2 Page")
Set DataRange = wsDest.Range("A13:Q61,A66:Q" & DestLastRow)
Case ("Reset Drawing No. 3 Page")
Set DataRange = wsDest.Range("A13:Q61,A66:Q122,A127:Q" & DestLastRow)
Case ("Reset Drawing No. 4 Page")
Set DataRange = wsDest.Range("A13:Q61,A66:Q122,A127:Q183,A188:Q" & DestLastRow)
Case ("Reset Drawing No. 5 Page")
Set DataRange = wsDest.Range("A13:Q61,A66:Q122,A127:Q183,A188:Q244,A249:Q" & DestLastRow)
End Select

If Not DataRange Is Nothing Then
Dim area As Range
For Each area In DataRange.Areas
 GetPartInfoForRange area.Columns(5), area.Columns(2), ODict
Next area
Reset_Drawing_Numbers.Value = "Reset Drawing Numbers"
End If
End Sub
Sub GetPartInfoForRange(lookupRange As Range, outputRange As Range, DataSet As Object)
 Dim cell As Range
 Dim counter As Long
 Dim ODict As Object
 
 For Each cell In lookupRange.Cells
 counter = counter + 1
 outputRange.Cells(counter) = GetPartInfo(DataSet, cell.Value)
 Next cell

End Sub
Private Function GetPartInfo(ByRef ODict As Object, sKey As String)
Dim Output As String
Output = ""
If ODict.Exists(sKey) Then
Output = ODict(sKey)
End If
GetPartInfo = Output

End Function
Private Function GetDictionary(Rng As Range, keyCol As Long, valCol As Long) As Object
Dim rCell As Range
Dim ODict As Object
Dim keyCells As Range

Set keyCells = Rng.Columns(keyCol).Cells
Dim valueCells As Range

Set valueCells = Rng.Columns(valCol).Cells
Set ODict = CreateObject("Scripting.Dictionary")
For Each rCell In keyCells
 Dim counter As Long
 counter = counter + 1
 If Not ODict.Exists(rCell.Value) Then
 ODict.Add rCell.Value, valueCells.Cells(counter).Value
 
 End If
 
Next rCell
Set GetDictionary = ODict
End Function

Private Sub Return_to_Job_card_Master_Click()
ThisWorkbook.Worksheets("Job Card Master").Activate
End Sub
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Watch MrExcel Video

Forum statistics

Threads
1,130,169
Messages
5,640,552
Members
417,151
Latest member
ChickenTenderer

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