VLOOKUP

[WAYNE]

Using Excel 97

I have another software program that produces a parts
list (Column A) and a corresponding quantity (Column B)

I need to look up a part number in column A (part No
109000) and set its quantity to zero (column B).

I can manually set a formula in another cell (column
C) "IF(A17=109000,(VLOOKUP(A17,$A$17:$B$1000,2,FALSE)))"
and "drag" the formula into the cells beneath in its
column.

This function gives a value of value of "0" as required
if the part number equals 109000 and returns the correct
quantity of all other part numbers until the second parts
list is encountered then it all goes up in smoke!!!!

Ultimately I need to .:-

1) get the second, third etc parts list to work
2) and secondly try to automate this in to a code module

Any ideas???

Part № Qty New Qty
101106 18 18
101109 24 24
101112 6 6
101118 84 84
101127 5 5
101133 8 8
101500 290 290
102521 97 97
102622 176 176
103312 28 28
103975 169 169
107851 11 11
107852 1 1
109000 20 0
109010 21 21
109015 146 146
#N/A
Part № Qty Qty
101115 3 3
101118 2 84
101500 10 290
102236 2 2
102521 5 97
102622 26 176
103975 26 169
109000 50 0
109020 7 7
109030 19 19
109108 4 4
109112 2 2
109118 30 30

 

[Cecilkumara Fernando]

WAYNE,
Why Vlookup? Just use
=IF(A17=109000,0,B17)
Cecil

 

[Dave Peterson]

I think one manual way of doing this is to apply data|filter|autofilter.

Filter on just the (one?) part number that should be 0 and change it manually.

If I wanted a macro, I think I'd use the Find method (like Edit|Find):

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

This is pretty much taken out of the help for Find.