VLOOKUP PART 2....(Dave Paterson)

[wayne]

The folowing code was supplied by Dave Paterson....Many
Thanks

I Can only get this code to work when I replace "sheet1"
with the actual sheet tab name ie "BOM Summary file1"
As the "sheet tab name" changes for each job / file why
shouldn't "Sheet1" overide the tab name????

Am I doing something wrong???

Option Explicit
Sub testme01()
Dim FoundCell As Range
Dim FirstAddress As String
Dim FindWhat As String

FindWhat = "109000"

With Worksheets("sheet1").Range("a:a")
Set FoundCell = .Cells.Find(what:=FindWhat, _
after:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, lookat:=xlWhole)

If FoundCell Is Nothing Then
'do nothing
Else

FirstAddress = FoundCell.Address
Do
FoundCell.Offset(0, 1).Value = 0
Set FoundCell = .FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address <> FirstAddress
End If
End With
End Sub

 

[Dave Peterson]

You're not doing anything wrong...

But if you want to run the macro against the activesheet, then you can make sure
the correct worksheet is selected and change this line:

With Worksheets("sheet1").Range("a:a")
to
with activesheet.range("a:a")

 

[Dave Peterson]

Peterson. Dave Peterson.
(said in a Sean Connery voice.)

 

[Tom Ogilvy]

If you want to use the code name of the sheet

With Sheet1.Range("a:a")

Worksheets uses the tab name - so when you supply "sheet1" to that, it is
looking for a sheet with sheet1 as the tab name.

If you lose your code example, you can find it in help under the findnext
help example.

 

[wayne]

"Money Penny give dave a big pat on the back...."

 

[Dave Peterson]

James Bond: I thought you might enjoy one of these.
[gives Ms. Moneypenny a cigar]
Ms. Moneypenny: How romantic. I know exactly where to put that.
[throws the cigar in the garbage]
James Bond: Oh Moneypenny, the story of our relationship: close, but no cigar.

http://www.imdb.com/title/tt0143145/quotes

 

[Debra Dalgleish]

"Ah yes, the legendary 007 wit, or at least half of it."

James Bond: I thought you might enjoy one of these.
[gives Ms. Moneypenny a cigar]
Ms. Moneypenny: How romantic. I know exactly where to put that.
[throws the cigar in the garbage]
James Bond: Oh Moneypenny, the story of our relationship: close, but no cigar.

http://www.imdb.com/title/tt0143145/quotes

"Money Penny give dave a big pat on the back...."


replace "sheet1"

 

[Dave Peterson]

Just in case people see that as an insensitive remark:

[After Q introduces Bond to his successor]
James Bond: If you're Q, does that make him R?
R: Ah yes, the legendary 007 wit, or at least half of it.

On the other hand, you may not have been quoting!!!!

(hehehe)

"Ah yes, the legendary 007 wit, or at least half of it."

James Bond: I thought you might enjoy one of these.
[gives Ms. Moneypenny a cigar]
Ms. Moneypenny: How romantic. I know exactly where to put that.
[throws the cigar in the garbage]
James Bond: Oh Moneypenny, the story of our relationship: close, but no cigar.

http://www.imdb.com/title/tt0143145/quotes

"Money Penny give dave a big pat on the back...."


-----Original Message-----
Peterson. Dave Peterson.
(said in a Sean Connery voice.)

<<snipped>>