PDA

View Full Version : VBA search through workbooks to return answer help



chris_uk_lad
05-06-2008, 10:23 AM
Hi, im having difficulty with knowing where to start and hopefully some of you guys can help me.
Im trying to search through several worksheets in a workbook to find a field that matches my original field, and if it does, allows me to place a Yes in the row next to it on the first worksheet aswell as retrieve the ID number belonging to it.

e.g
First worksheet:

ID Name Column ID Exist?
1111 John

Second Worksheet:

Column ID Name REF NO
BBB John 1111

So with some code i could search through the table looking for ID 1111, retrieve the column ID, and add a 'Y' to the exist field. I ask for VB code cause i want it to run through every field in several worksheets, kind of like a cleanup tool.
Many Thanks

mdmackillop
05-06-2008, 10:28 AM
Hi Chris,
Welcome to VBAX,
If you can post a sample workbook, we can give a better answer. Use Manage Attachments in the Go Advanced section.
Regards
MD

mikerickson
05-06-2008, 10:38 AM
Dim SheetNames as Variant, oneSheetName as Variant
Dim seekForCell as Range, foundCell as Range

SheetNames = Array("Sheet1", "Sheet2", "Sheet3")

Set seekForCell = ThisWorkbook.Sheets("home").Range("A1")

For Each oneSheetName in SheetNames
On Error Resume Next
With ThisWorkbook.Sheets(oneSheetName)
Set foundCell = .Range("C:C").Find(what:= SeekForCell.Value, After:=.Range("C1"))
End With
On Error Goto 0

If Not (foundCell Is Nothing) Then
SeekForCell.Offset(0,2) = "Found on Sheet " & foundCell.Parent.Name
Exit For
End If
Next oneSheetName

chris_uk_lad
05-06-2008, 10:40 AM
Attatched some sample data. I want to code to search through the "In-House" and "External" worksheets to find the Ref No of all users and return the Column ID to the "Crossing Record" sheet to input in the field that corresponds. 8640

chris_uk_lad
05-06-2008, 01:38 PM
hi, thanks for he code snippet, just want to check if that will work for the sample database i posted, and if you could explain it slightly? sorry for askin so much :X

mikerickson
05-06-2008, 03:30 PM
It is not based on your spreadsheet.
It looks for the value of home!A1 in column C of Sheet1, Sheet2, and Sheet 3.
If that value is found, the fact is recorded in home!C1

chris_uk_lad
05-07-2008, 09:52 AM
Ive had a go at modifying the code you supplied me and have got it scanning through the test document to see if and where the files exist yet am not able to return one of the fields with the name (i.e find the person with the matching ID No and return their name or reference number in a further cell.). I also think the count function ive implemented for scanning through all the rows in "A" might be used incorrectly :s


Sub tester()
Dim SheetNames As Variant, oneSheetName As Variant
Dim seekForCell As Range, foundCell As Range
Dim newcount As Integer, Count As Integer
'Dim columnID As Variant
Dim columnID As Range

Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Count = Selection.Cells.Count
Range("A1").Select
LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
SheetNames = Array("Sheet2", "Sheet3")
newcount = 1
While newcount <= Count
Set seekForCell = ThisWorkbook.Sheets("Sheet1").Range("A" & newcount) 'just need range to change
For Each oneSheetName In SheetNames 'moves through sheet by sheet
On Error Resume Next
With ThisWorkbook.Sheets(oneSheetName)
Set foundCell = .Range("C:C").Find(what:=seekForCell.Value, After:=.Range("C" & Count)) 'C:C just means column C
Set columnID = Worksheets(oneSheetName).Range("A1")
End With
On Error GoTo 0
If Not (foundCell Is Nothing) Then 'If foundcell is correct
seekForCell.Offset(0, 2) = "Found on Sheet " & foundCell.Parent.Name 'when value found, output "Found sheet Sheet#"
With ThisWorkbook.Sheets(oneSheetName)
seekForCell.Offset(0, 3) = columnID
End With
Exit For
Else
seekForCell.Offset(0, 2) = "Not Found"
End If
Next oneSheetName
newcount = newcount + 1
Wend
End Sub

Any help greatly appreciated, attatched test database also.
8654

mdmackillop
05-07-2008, 10:36 AM
Hi Chris,
When you post code, please select it and click the VBA button to format it as shown