| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP formula
Dear Helper,
I have the following script to add a formular to a cell in all the sheets in Workbook(final_bk) and copy down to the last used cells in the respective sheet. The script stop at vLookup line, how should I set the formula ? For Each sh In Workbooks(final_bk).Worksheets sh.Activate If sh.Name < "Summary" Then ActiveSheet.UsedRange.Select numcolumns = Selection.Columns.Count numRows = Selection.Rows.Count Cells(2, numcolumns + 1).Formula = "=IF(A2<"",VLOOKUP(A2,[WORKBOOKS(source_book)]sheets("Source")!$A:$B,2,false)" Cells(2, numcolumns + 1).AutoFill Destination:=Range("") Columns(numcolumns + 1).Copy Columns(numcolumns + 1).PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False End If Next sh |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VLOOKUP formula
All quotes within quotes (that is, all quotes in your worksheet formula)
should be entered as two quotes. -- Kind regards, Niek Otten "ak" wrote in message ... Dear Helper, I have the following script to add a formular to a cell in all the sheets in Workbook(final_bk) and copy down to the last used cells in the respective sheet. The script stop at vLookup line, how should I set the formula ? For Each sh In Workbooks(final_bk).Worksheets sh.Activate If sh.Name < "Summary" Then ActiveSheet.UsedRange.Select numcolumns = Selection.Columns.Count numRows = Selection.Rows.Count Cells(2, numcolumns + 1).Formula = "=IF(A2<"",VLOOKUP(A2,[WORKBOOKS(source_book)]sheets("Source")!$A:$B,2,false)" Cells(2, numcolumns + 1).AutoFill Destination:=Range("") Columns(numcolumns + 1).Copy Columns(numcolumns + 1).PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False End If Next sh |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VLOOKUP formula
Thanks Niek
I tried but still received error. The error code is "1004" "Niek Otten" 來函： All quotes within quotes (that is, all quotes in your worksheet formula) should be entered as two quotes. -- Kind regards, Niek Otten "ak" wrote in message ... Dear Helper, I have the following script to add a formular to a cell in all the sheets in Workbook(final_bk) and copy down to the last used cells in the respective sheet. The script stop at vLookup line, how should I set the formula ? For Each sh In Workbooks(final_bk).Worksheets sh.Activate If sh.Name < "Summary" Then ActiveSheet.UsedRange.Select numcolumns = Selection.Columns.Count numRows = Selection.Rows.Count Cells(2, numcolumns + 1).Formula = "=IF(A2<"",VLOOKUP(A2,[WORKBOOKS(source_book)]sheets("Source")!$A:$B,2,false)" Cells(2, numcolumns + 1).AutoFill Destination:=Range("") Columns(numcolumns + 1).Copy Columns(numcolumns + 1).PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False End If Next sh |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VLOOKUP formula
This line actually places the formula in the cell:
Cells(2, numcolumns + 1).Formula = _ "=IF(A2<"",VLOOKUP(A2,[WORKBOOKS(source_book)]sheets("Source")!$A:$B,2,false)" But that's not the correct syntax for the formula--you wouldn't write that formula in a cell and expect it to work. I like to let excel do the work for me: dim TableRng as range ..... set tablerng = workbooks(source_book).worksheets("source").range( "a:b") ..... cells(2,numcolumns + 1).formula = _ "=if(a2="""","""",vlookup(a2," _ & tablerng.address(external:=true) _ & ",2,false)" Watch your double quotes, too. ak wrote: Dear Helper, I have the following script to add a formular to a cell in all the sheets in Workbook(final_bk) and copy down to the last used cells in the respective sheet. The script stop at vLookup line, how should I set the formula ? For Each sh In Workbooks(final_bk).Worksheets sh.Activate If sh.Name < "Summary" Then ActiveSheet.UsedRange.Select numcolumns = Selection.Columns.Count numRows = Selection.Rows.Count Cells(2, numcolumns + 1).Formula = "=IF(A2<"",VLOOKUP(A2,[WORKBOOKS(source_book)]sheets("Source")!$A:$B,2,false)" Cells(2, numcolumns + 1).AutoFill Destination:=Range("") Columns(numcolumns + 1).Copy Columns(numcolumns + 1).PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False End If Next sh -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VLOOKUP formula
Hi Dave,
I tried to amend the formula as advised but same error. Will there be problem if I use excel2000? Tks. Alice "Dave Peterson" wrote: This line actually places the formula in the cell: Cells(2, numcolumns + 1).Formula = _ "=IF(A2<"",VLOOKUP(A2,[WORKBOOKS(source_book)]sheets("Source")!$A:$B,2,false)" But that's not the correct syntax for the formula--you wouldn't write that formula in a cell and expect it to work. I like to let excel do the work for me: dim TableRng as range ..... set tablerng = workbooks(source_book).worksheets("source").range( "a:b") ..... cells(2,numcolumns + 1).formula = _ "=if(a2="""","""",vlookup(a2," _ & tablerng.address(external:=true) _ & ",2,false)" Watch your double quotes, too. ak wrote: Dear Helper, I have the following script to add a formular to a cell in all the sheets in Workbook(final_bk) and copy down to the last used cells in the respective sheet. The script stop at vLookup line, how should I set the formula ? For Each sh In Workbooks(final_bk).Worksheets sh.Activate If sh.Name < "Summary" Then ActiveSheet.UsedRange.Select numcolumns = Selection.Columns.Count numRows = Selection.Rows.Count Cells(2, numcolumns + 1).Formula = "=IF(A2<"",VLOOKUP(A2,[WORKBOOKS(source_book)]sheets("Source")!$A:$B,2,false)" Cells(2, numcolumns + 1).AutoFill Destination:=Range("") Columns(numcolumns + 1).Copy Columns(numcolumns + 1).PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False End If Next sh -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VLOOKUP formula
The formula should work in xl2k, also.
What did you use? ak wrote: Hi Dave, I tried to amend the formula as advised but same error. Will there be problem if I use excel2000? Tks. Alice "Dave Peterson" wrote: This line actually places the formula in the cell: Cells(2, numcolumns + 1).Formula = _ "=IF(A2<"",VLOOKUP(A2,[WORKBOOKS(source_book)]sheets("Source")!$A:$B,2,false)" But that's not the correct syntax for the formula--you wouldn't write that formula in a cell and expect it to work. I like to let excel do the work for me: dim TableRng as range ..... set tablerng = workbooks(source_book).worksheets("source").range( "a:b") ..... cells(2,numcolumns + 1).formula = _ "=if(a2="""","""",vlookup(a2," _ & tablerng.address(external:=true) _ & ",2,false)" Watch your double quotes, too. ak wrote: Dear Helper, I have the following script to add a formular to a cell in all the sheets in Workbook(final_bk) and copy down to the last used cells in the respective sheet. The script stop at vLookup line, how should I set the formula ? For Each sh In Workbooks(final_bk).Worksheets sh.Activate If sh.Name < "Summary" Then ActiveSheet.UsedRange.Select numcolumns = Selection.Columns.Count numRows = Selection.Rows.Count Cells(2, numcolumns + 1).Formula = "=IF(A2<"",VLOOKUP(A2,[WORKBOOKS(source_book)]sheets("Source")!$A:$B,2,false)" Cells(2, numcolumns + 1).AutoFill Destination:=Range("") Columns(numcolumns + 1).Copy Columns(numcolumns + 1).PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False End If Next sh -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I combine a VLOOKUP formula and a NETWORKDAYS formula? | Excel Worksheet Functions | |||
| Alternative formula to the vlookup formula? | Excel Worksheet Functions | |||
| convert vlookup formula to link formula | Excel Worksheet Functions | |||
| Excel 2002 VLOOKUP formula or other formula | Excel Discussion (Misc queries) | |||
| VLookup Formula | Excel Worksheet Functions | |||
Linear Mode
