|
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 |
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 04:13 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com