FIND a value in a Workbook

Closed
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hello,

I'm writing a macro, in part of the macro I'm trying to search for values that are present in one workbook (lets call it Workbook A) and looking for the value in other workbook (Workbook B). The each value is present on a different tab of Workbook B. I want the sheet that is present to be activiated and then I am searching through a column for a variable I have called ColumnHeading. My current attempt looks something like this:

If Cells.Find(What:=Cusip(n), After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Found = True Then
k = 0
Do
Windows(DocMain).Activate
ActiveWorkbook.Sheets(Cusip(n)).Select
ActiveWorkbook.Sheets(Cusip(n)).Tab.ColorIndex = 3
k = k+1
Loop While (ActiveSheet.Columns(2).Cells(k) <> ColumnHeading)

1 reply

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
As you seem to familiar with writing a macro try these ideas
you need not activate a sheet or cell or even workbook
for this you can use
"with" statement with ofcourse and "end with "
you can loop through the sheets in Book2 to find the entry you ahvae in Book1.
It is also not clear if you do find a value in boo1, in book 2 what do you want to do with the found item.

On these ideas would you like to rewrite the macro?