vlookup lookup different column
 

No, I'm sorry, but I don't. Code doesn't "sit in [a] column". It's in a
module.

On 29 Oct 2004 21:45:52 -0700, (tango) wrote:

Dear Myrna,
I do not understand you. you see, if i follow my table column then i
will not get the value. originally the code is sitting in column B so
if in my vba i put B then i will not get anything. when i debug i
found out the code is sitting in column A. that is why in my vba i
refer to A and not B. do u see the point?
thanks alot





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.

On 29 Oct 2004 19:50:18 -0700, (tango) wrote:

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