vlookup lookup different column

[tango]

Dear All, may i know why the lookup value cell is referring to A and
not B? in my sheet the lookup value is in column B but why in my code
it refers to column A? i was given #ref when refer to B for lookup
value. so i did debug and found out it stores in column A. can have
your advice? thanks alot


column A column B
PROCUMENT DEPARTMENT COST 21000
CARBON STEEL 21100
CARBON STEEL - SHELL PLATE SA516-70N 21110
CARBON STEEL - HEADS 21120

Private Sub CommandButton1_Click()
Dim i As Long
Dim z As Range
Dim tableA As Range
Dim tableB As Range
Dim y

Set tableA = Range("A1:A100")
Set tableB = Sheet8.Range("$C$1:$D$2")

For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
X = tableA.Cells(i, "A").Value
Set z = tableA.Cells(i, "B")
If Cells(i, "A").Value = "" Then
' accumulate amount in TableB
Else
y = Application.VLookup("*" & X & "*", tableB, 2, False)
z.Value = y
End If
Next i

End Sub

 

[Myrna Larson]

Because it's nearly impossible to figure out the layout from your message --
where column A ends and column B begins. Where are the numbers? Column C? You
don't mention column C.