VLOOKUP formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
All quotes within quotes (that is, all quotes in your worksheet formula)
should be entered as two quotes.
 
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 said:
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
Click to expand...
Click to expand...
 
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.


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
Click to expand...
 
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 said:
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.


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
Click to expand...
Click to expand...
 
The formula should work in xl2k, also.

What did you use?
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 said:
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.


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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top