VLOOKUP PART 2....(Dave Paterson)
 

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

VLOOKUP PART 2....(Dave Paterson)
 

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")

wayne wrote:

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

VLOOKUP PART 2....(Dave Paterson)
 

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


wayne wrote:

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

VLOOKUP PART 2....(Dave Paterson)
 

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.


--
regards,
Tom Ogilvy

"wayne" wrote in message
...
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

VLOOKUP PART 2....(Dave Paterson)
 

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



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


wayne wrote:

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

.

VLOOKUP PART 2....(Dave Paterson)
 

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

wayne wrote:

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

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


wayne wrote:

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

.


--

Dave Peterson

VLOOKUP PART 2....(Dave Paterson)
 

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

Dave Peterson wrote:
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

wayne wrote:

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


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


wayne wrote:

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

.





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

VLOOKUP PART 2....(Dave Paterson)
 

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)

Debra Dalgleish wrote:

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

Dave Peterson wrote:
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

wayne wrote:

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


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


<<snipped