vlookup code and accumulate - sorry need help

[tango]

dear all/frank,
i am so sorry need to bug you all but i really hope can have the help
from here.
i do not know why my vlookup cannot work in vba. if i type in the
sheet as below can work nicely. pls help.

this can work.... but i cannot ask user to do this...
=IF(B15<>"",IF(ISNA(VLOOKUP("*"&B15&"*",Sheet4!$A$2:$B$300,2,FALSE)),"Item
Not Found In COA",VLOOKUP("*"&B15&"*",Sheet4!$A$2:$B$300,2,FALSE)),"")



this cannot work..... always return 21000 and also cannot return value
in current sheet. z cannot assign value to cell. nothing show

Private Sub CommandButton1_Click()
Dim i As Long

Set TableA = Range("B15:B168")
Set tableB = Sheet4.Range("$A$2:$B$300")

'Set tableb = Range("Sheet4!$A$2:$B$300")
X = TableA.Cells(i, "B").Value
'Z = TableA.Cells(i, "U").Value
z = TableA.Cells(i, "U")


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

sheet1

desc cannot update fr sheet4 accumulate and put other
sheet by item
HEMI HEAD Item Not Found In COA 383695.49

SHELL PLATE 21110 1653551.84
SHELL PLATE 21110 319919.90
SHELL PLATE 21110 59204.15

STIFFERNER RING Item Not Found In COA 164021.43
ELLIPSOIDAL HEAD Item Not Found In COA 4887.35

SHELL PLATE 21110 9945.34



sheet4 A2:B300
Description Cost Code
PROCUMENT DEPARTMENT COST 21000
CARBON STEEL 21100
CARBON STEEL - SHELL PLATE 21110
CARBON STEEL - HEADS 21120
CARBON STEEL - SKIRTS/SAD 21130
CARBON STEEL - PIPES 21140
CARBON STEEL - FITTINGS 21150
CARBON STEEL - VALVE 21160
CARBON STEEL - TUBE SHEET 21170
CARBON STEEL - TUBES/U-TU 21180
CARBON STEEL - FLANGES/FO 21190

 

[JulieD]

Hi Tango

You might like to change

Application.VLookup
to
Application.WorksheetFunction.VLookup
and see if that solves your problem

Cheers
JulieD

 

[Frank Kabel]

Hi
without testing it try changing the following:

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("B15:B168")
Set tableB = Sheet4.Range("$A$2:$B$300")

'Set tableb = Range("Sheet4!$A$2:$B$300")
X = TableA.Cells(i, "B").Value
'Z = TableA.Cells(i, "U").Value

For i = 15 To Cells(Rows.Count, "B").End(xlUp).Row
set z = TableA.Cells(i, "U")
If Cells(i, "B").Value = "" Then
' accumulate amount in TableB
Else
y = Application.VLookup("*" & X & "*", tableB, 2, False)
z.value = y 'put code in TableA
End If
Next i