Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup code and accumulate - sorry need help
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,FALS E)),"") 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup code and accumulate - sorry need help
Hi Tango
You might like to change Application.VLookup to Application.WorksheetFunction.VLookup and see if that solves your problem Cheers JulieD "tango" wrote in message om... 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,FALS E)),"") 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup code and accumulate - sorry need help
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 "tango" wrote: 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,FALS E)),"") 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Accumulate values | Setting up and Configuration of Excel | |||
accumulate | Excel Discussion (Misc queries) | |||
accumulate totals | Excel Worksheet Functions | |||
how to accumulate value and put in other sheet | Excel Worksheet Functions | |||
how to accumulate value and put in other sheet | Excel Programming |