This code is now working to search multiple values in multiple sheets. How can I fix it to support searching multiple values at the same time without having to write every one . For example, I want to put in column A all my search values, and then I click on search, and it should search and give the value for all of them at the same time. What should I change in the code to do this function? Please see the code and the images.
Dim i, j, k, l, m, n, no_sheets As Variant
Dim key, cursor, sheetname As Variant
Dim flag As Variant
Dim sheet1_count, sheet1_row, row_count As Integer
Dim Arr() As Variant
sheet1_count = WorksheetFunction.CountA(ThisWorkbook.Worksheets("sheet1").Range("A:A"))
no_sheets = 3 ' Number of sheets
k = 2
sheet1_row = sheet1_count 'My start in result sheet
key = ThisWorkbook.Worksheets("sheet1").Range("A" & sheet1_count) ' The value that the user will put in searching sheet in column A
For i = 2 To no_sheets ' sheet2 then sheet3 then sheet4 then sheet5 ..etc
flag = False
sheetname = "Sheet" & i
row_count = WorksheetFunction.CountA(ThisWorkbook.Worksheets(sheetname).Range("A:A")) ' It's a counter that will contain the range of row A in each sheet
For j = 1 To row_count 'I'll start from row 1 until the last sheet
cursor = ThisWorkbook.Worksheets(sheetname).Range("A" & j) 'Searching in column A in each sheet (1st row - last row) and put the value in this variable
If key = cursor Then ' If the entering value in sheet1 equal the value that we have in current sheet, do the following
' Copying the data
flag = True ' The data found
ThisWorkbook.Worksheets("sheet1").Range("A" & sheet1_row) = ThisWorkbook.Worksheets(sheetname).Range("A" & j)
ThisWorkbook.Worksheets("sheet1").Range("B" & sheet1_row) = ThisWorkbook.Worksheets(sheetname).Range("B" & j)
ThisWorkbook.Worksheets("sheet1").Range("C" & sheet1_row) = ThisWorkbook.Worksheets(sheetname).Range("C" & j)
ThisWorkbook.Worksheets("sheet1").Range("D" & sheet1_row) = ThisWorkbook.Worksheets(sheetname).Range("D" & j)
ThisWorkbook.Worksheets("sheet1").Range("E" & sheet1_row) = ThisWorkbook.Worksheets(sheetname).Range("E" & j)
ThisWorkbook.Worksheets("sheet1").Range("F" & sheet1_row) = ThisWorkbook.Worksheets(sheetname).Range("F" & j)
sheet1_row = sheet1_row + 1
Else
End If
Next j 'Go to the next row
Next i 'Go to the next sheet
MsgBox "finished, Do another search..!"
If key <> cursor Then
flag = False ' If the value not found
ThisWorkbook.Worksheets("sheet1").Range("B" & sheet1_row) = "Not found"
ThisWorkbook.Worksheets("sheet1").Range("C" & sheet1_row) = "Not found"
ThisWorkbook.Worksheets("sheet1").Range("D" & sheet1_row) = "Not found"
ThisWorkbook.Worksheets("sheet1").Range("E" & sheet1_row) = "Not found"
End If
End Sub
Sub MatchUnMatch_Click()
Dim i, j, k, l, m, n As Integer
Dim ListA_count, ListB_count, ListC_count, ListD_count, ListE_count As Integer
Dim key, cursor As String
Dim flag As Boolean
ListA_count = WorksheetFunction.CountA(ThisWorkbook.Worksheets("MatchUnmatch").Range("A:A"))
ListB_count = WorksheetFunction.CountA(ThisWorkbook.Worksheets("MatchUnmatch").Range("B:B"))
'ListA_count = ThisWorkbook.Worksheets("MatchUnMatch").Range("A2").End(xlDown).Row
'MsgBox ListA_count & " " & ListB_count
'=======================================================================================================
'
'
' Matching Logic for List 'A' and List 'B'
'
'
'=======================================================================================================
k = 2
For i = 2 To ListA_count
key = ThisWorkbook.Worksheets("MatchUnMatch").Range("A" & i)
For j = 1 To ListB_count
cursor = ThisWorkbook.Worksheets("MatchUnMatch").Range("B" & j)
'MsgBox "Key=" & Key & " Cursor=" & cursor
If key = cursor Then
ThisWorkbook.Worksheets("MatchUnMatch").Range("C" & k) = key
k = k + 1
Exit For
End If
Next j
Next i
'=======================================================================================================
'
'
' List 'A' items not in List 'B'
'
'
'=======================================================================================================
ListC_count = WorksheetFunction.CountA(ThisWorkbook.Worksheets("MatchUnmatch").Range("C:C"))
k = 2
For i = 2 To ListA_count
key = ThisWorkbook.Worksheets("MatchUnMatch").Range("A" & i)
flag = False
For j = 1 To ListC_count
cursor = ThisWorkbook.Worksheets("MatchUnMatch").Range("C" & j)
If key = cursor Then
flag = True
Exit For
End If
Next j
If flag = False Then
ThisWorkbook.Worksheets("MatchUnMatch").Range("D" & k) = key
k = k + 1
End If
Next i
'=======================================================================================================
'
'
' List 'B' items not in List 'A'
'
'
'=======================================================================================================
k = 2
For i = 2 To ListB_count
key = ThisWorkbook.Worksheets("MatchUnMatch").Range("B" & i)
flag = False
For j = 1 To ListC_count
cursor = ThisWorkbook.Worksheets("MatchUnMatch").Range("C" & j)
If key = cursor Then
flag = True
Exit For
End If
Next j
If flag = False Then
ThisWorkbook.Worksheets("MatchUnMatch").Range("E" & k) = key
k = k + 1
End If
Next i
End sub
see the image please, to understand what I meanI want to put in row A in search sheet (first sheet) many numbers and then I want to click on search button one time only that should give me all the values at the same time.I don't want to click one search more than one time. I want someone to fix it for me please. As soon as possible :(
(*) updated after OP's requested functionality to save data from previous runs and have numbers not found in "data" sheets marked as "NOT FOUND"
(**) updated after OP's request to handle a variable number of columns
(***) updated to fix FindItems() function to handle non contiguous cells range
(****) updated to fix iRow updating in sub Main()
(*****) updated to have items to be searched in sheets whose cell "A1" has same content as that of "base" sheets
(******) updated to have items to be searched in column A of all data sheets, whatever the header of that column
While I was doing my code, Cornel's already given you an answer which is ok
however should you ever want to manage:
any different number of "data" Sheets (i.e.: sheets to seek for item number in its column "A" and gather relevant data from adjacent columns)
multiple occurrences of a "number" in any "data" sheet
(*) functionality to save previous data already in "base" sheet resulting from previous runs
(*) functionality to mark "NOT FOUND" in "base" sheet when number not found on any "data" sheet
(**) functionality to handle a variable number of columns
then you may want to use the following code
Option Explicit
Sub main()
Dim items() As Variant, itemToFind As Variant
Dim itemsNumber As Long, previousDataNumber As Long, dataShtNumber As Long, iRow As Long, i As Long, j As Integer
Dim itemsSht As Worksheet, dataShts() As Worksheet
Dim rngToCopy As Range
Dim itemFound As Boolean
Dim columnsNumberToCopyAndPaste As Long
columnsNumberToCopyAndPaste = 7 '<== here you set the number of columns to be copied form "data" sheet and pasted in "base" sheet
Set itemsSht = ThisWorkbook.Worksheets("Sheet1") ' this is the "base" sheet you take "numbers" from its column A, starting at row 2
Call GetItems(itemsSht, items(), itemsNumber, previousDataNumber) ' gather all "numbers" to be searched for in "data" sheets
Call GetDataWorksheets(dataShts(), ThisWorkbook, "Sheet1", dataShtNumber) ' gather all "data" sheets
iRow = 1
For i = 1 To itemsNumber 'loop through "numbers"
itemToFind = items(i) ' "number" to be searched for in "data" sheets
itemFound = False
For j = 1 To dataShtNumber 'loop through "data" worksheets
Set rngToCopy = FindItems(dataShts(j), itemToFind, 1, columnsNumberToCopyAndPaste) ' get "data" sheet column 1 cells with "number" along with 'columnsNumberToCopyAndPaste-1' adjacents cells
If Not rngToCopy Is Nothing Then ' if found any occurrence of the "number" ...
rngToCopy.Copy itemsSht.Cells(1, 1).Offset(previousDataNumber + iRow) ' ... copy it and paste into "base" sheet
iRow = iRow + rngToCopy.Count / columnsNumberToCopyAndPaste 'update "base" sheet row offset to paste subsequent cells, if any
itemFound = True
End If
Next j
If Not itemFound Then 'if NOT found any occurrence of the "number" ...
itemsSht.Cells(1, 1).Offset(previousDataNumber + iRow).Value = itemToFind
itemsSht.Cells(1, 2).Offset(previousDataNumber + iRow).Resize(1, columnsNumberToCopyAndPaste - 1).Value = "NOT FOUND"
iRow = iRow + 1
End If
Next i
itemsSht.Columns.AutoFit
End Sub
Sub GetItems(itemsSht As Worksheet, items() As Variant, itemsNumber As Long, previousDataNumber As Long)
With itemsSht
previousDataNumber = .Cells(.Rows.Count, 2).End(xlUp).Row - 1
itemsNumber = .Cells(.Rows.Count, 1).End(xlUp).Row - 1 - previousDataNumber
ReDim items(1 To itemsNumber) As Variant
With .Cells(2 + previousDataNumber, 1).Resize(itemsNumber)
If itemsNumber = 1 Then
items(1) = .Value
Else
items = WorksheetFunction.Transpose(.Value)
End If
End With
End With
End Sub
Function FindItems(sht As Worksheet, itemToFind As Variant, columnToSearchFor As Long, columnsToCopy As Long) As Range
Dim cell As Range, unionRng As Range
Dim firstAddress As String
With sht.Columns(columnToSearchFor)
Set cell = .Find(What:=itemToFind, LookAt:=xlWhole)
If Not cell Is Nothing Then
firstAddress = cell.Address
Set unionRng = cell.Resize(, columnsToCopy)
Do
Set unionRng = Union(unionRng, cell.Resize(, columnsToCopy))
Set cell = .FindNext(cell)
Loop While Not cell Is Nothing And cell.Address <> firstAddress
Set FindItems = unionRng
End If
End With
End Function
Sub GetDataWorksheets(shts() As Worksheet, wb As Workbook, noShtName As String, nShts As Long)
Dim sht As Worksheet
For Each sht In wb.Worksheets
With sht
If .Name <> noShtName Then
nShts = nShts + 1
ReDim Preserve shts(1 To nShts) As Worksheet
Set shts(nShts) = sht
End If
End With
Next sht
End Sub
(*) Actually I added a previousDataNumber variable to track data already there at the time the routine runs
(**) in columnsNumberToCopyAndPaste = 5 you set the number of columns to be handled
I split it into a "main" sub and some other "helper" subs or function in order to have clear and more maintainable/changeable code.
this habit has always helped me much more than I could ever expect at my beginnings, when I was used to code looong subs