tweak macro code - use "Find"

[Christine]

Hello,

Thanks in advance for your insight.

I need to run a loop that finds the value of a cell, takes
that value, and searches for it in another sheet. I have
pretty limited knowledge of VBA so I created a macro, but
when I look at the code, it has the actual value for what
it is searching for. This value will change and I don't
want to use that value only. So how can I tell it that
whatever value it finds, to do a search on that?

Am I making any sense? In the "What:=" part, I want it to
find whatever value the previous code has selected, not
just "MI071001".

Here is the code:
Selection.Find(What:="MI071001", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

Please let me know if you have questions. Your help is
definitely appreciated.

Christine

 

[Don Guillett]

x=whatever was found by previous part of code
find(x,

 

[Dave Peterson]

You're looping through cells to find the value of each cell on another
worksheet?

Option Explicit
Sub testme01()

Dim myCell As Range
Dim myRng As Range
Dim FoundCell As Range

With Worksheets("sheet1")
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In myRng
With Worksheets("sheet2").Range("a:a")
Set FoundCell = .Find(what:=myCell.Value, _
after:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If FoundCell Is Nothing Then
'error message
MsgBox myCell.Value & " wasn't found"
Else
FoundCell.Interior.ColorIndex = 36
End If
End With
Next myCell

End Sub

 

[Christine]

Dave,

Thank you. I'm looping through cells to find their value.
For each value that I find, I want to go to another sheet,
find that value, and copy the contents of that row's
information to another file altogether.

Your response will give me something to work from.

Do you have any suggestions for books that are helpful for
someone just learning this kind of thing? I seem to find
myself working on these projects that use Excel VBA when I
really have little clue how to put everything together.

Thanks,

Christine

 

[Christine Flott]

Dave,

Thanks for your reply. I've gotten further than I have in
writing this macro of mine than I have on other projects
because of community help like this. I discovered Google's
groups a couple of days ago and have found them very
helpful.

I'll take a look at that add-in you suggested and will be
hitting the bookstore this weekend.

Thanks again,
Christine