Search excel to filter a list

[akaballa123]

Hi there,

Im new to excel vba but would like to learn it. I am currently using excel 2003. One of the projects I want to do is loop through the all the values in the first column in sheet A in workbook A. For each value in the column, I want to search in workbook B (in any sheets, row, or columns) and see if the current value in the column in sheet A in workbook A is in workbook B.

For Ex:

I want to search the string: "secret"

- "secret" is the first value in the first column (A1) in sheet A of workbook A.

- I am going to check to see if the value "secret" is found anywhere in workbook B ( regardless of the sheets)

- if it is found in workbook B i want to write the value "secret" in a new excel workbook that I created called "filterList".

- After this I will continue to loop through the other values in the next rows of column A in workbook A.

I am not too good with the syntax. Can someone please help me get started with this project. I just need a kickoff.

Thanks

 

[PHV]

What have YOU tried so far and where in YOUR code are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[AnotherHiggins]

First, it sounds like you have similar data chopped up in various sheets in Workbook B. That should be changed. All similar data should be put into a single table. Don't split up data based on day, week, month, employee, department, etc. Just have a column to distinguish between whatever groups you have.

What is the business case for this? If you explain what your real-life situation is, someone might be able to offer personalized advice on how to structure your data and proceed with your task.

As for the code - turn on your macro recorder (Tools > Macro > Record new macro) and play around doing different bits of what you want. Observe the code in the VB Editor.

Post back what you get for help cleaning it up and improving syntax. Macro Recorder is a wonderful tool with which to learn VBA.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[akaballa123]

this is what I have tried so far


Sub FilterList()

Dim valueCopy As Variant
Do
' check to see if the first row in the first column is not empty. If not empty then copt the current value and seach in filterList for the value
If Not Application.Workbooks("53067_device_details 1 .xls").Worksheets("53067_device_details 1").Range("A1").Offset(1, 0).Value = 0 Then

valueCopy = Application.Workbooks("53067_device_details 1 .xls").Worksheets("53067_device_details 1").Range("A1").Offset(1, 0).Value

Application.Workbooks("availability IP Core Nov, 2007.xls").Find(What:=valueCopy _
, After:=.Cells(0,0) , LookIn:xlValues, LookAt:xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)

'Pseudocode for the following
If value is found in Application.Workbooks("availability IP Core Nov, 2007.xls") Then

Do

valueCopy.Paste.Range(0,0).Offset(1,0)

Loop Until IsEmpty(ActiveCell.Offset(1, 0))

End If

End If

Loop Until IsEmpty(ActiveCell.Offset(1, 0))

End Sub