VLookup then delete....

[Wayne]

I need to lookup a part number in column A (109000) and
set the corresponding Quantity in column B to "0" from
its original.

I'vebeen able to "slowly" achieve this with an existing
workbook with various VLookup and nested if statements.
However I'd rather set this in a code module but not too
sure how to do this. At this point I can Dim the range
but then stumble to a halt! Who siad VB was easy...

Many thanks in advance...

Column A Column B
Part Number Quantity

102521 60
103975 56
109000 184
109020 32
109030 208

102622 36
103324 14
103975 36
109000 272
109020 40

107851 15
107852 1
109000 244
109020 40
109030 236

 

[Frank Kabel]

Hi Wayne
would be helpful if you post your existing formula. Maybe it's possible
to optimize this formula.
IMHO the VBA solution is probably not faster but slower than a
worksheet function

As a good site to get started with VBA have a look at
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Tom Ogilvy]

It is unclear how you can zero an existing value in a cell with a formula,
so this may not be correct, but it does what you describe
Dim sNum as String
Dim rng as Range
Dim res as Variant

sNum = InputBox("enter part number")
if isnumeric(sNum) then
set rng = Range(Cells(1,1),Cells(rows.count,1).End(xlup))
set res = application.Match(clng(sNum),rng,0)
if not iserror(res) then
rng(res,2).Value = 0
else
msgbox sNum & " was not found"
End if
else
msgbox "Your part number must be a number"
End if