ADO call to lookup table

[Dallman Ross]

In a macro in Excel 2002 I have this:

With DestSh
.Range("F2", Cells(LastR, 6)).Value = _
"=VLOOKUP(OFFSET(E$1,ROW()-1,0),'E:\Documents\Records\Finance\Securities\Research\[Symbol Lookup.xls]Symbol Lookup'!$A$2:$C$4000,3,FALSE)"
End With

It works. It populates column F with that formula, and the formula
works to look up stock names from the symbols in column E. This
works even though the lookup workbook is closed. However, the
macro pulls up a Windows Explorer Window set to that directory.
The workbook I want is highlighted, but Excel is waiting for me
to click on the book or hit Enter. Then the macro finishes.

I don't know why Excel is asking me for confirmation. I'd like it
to just work without a pause. Am I going about this the right way?
How can I stop the required interaction from me?

Also, I tried to set a string var in my macro to the long name so
I could use the var instead in the statement, but it doesn't work.
I can basically understand why it won't work, but I'd like to
know a reasonable approach. Oh! It dawns on me that I could
set a name in the worksheet and just use that in the macro. Hmmmmm.

Second question about this: some of the lookups fail with an #NA
error. I expected that. In the case of these errors, I want to
set those cells to a different VLOOKUP statement instead. I
don't know how to get my macro to do such a thing. (The second
VLOOKUP would be similar, but would start in column B instead of A.)

I'd also like to know how I can find the last row in my lookup
table. There are actually 2900-something entries in it now.
I don't need to say $A$2:$C$4000, but would rather say
$A$2 to lastrow in column C. Any ideas here would also
be most appreciated.

dman

 

[JLatham]

I'll tackle 2 pieces of your puzzle:

Ok, 2 1/2: In your code don't use .Value =, use .Formula =, you're setting
up a formula, not a value. So...
With DestSh
.Range("F2", Cells(LastR, 6)).Formula = _

"=VLOOKUP(OFFSET(E$1,ROW()-1,0),'E:\Documents\Records\Finance\Securities\Research\[Symbol Lookup.xls]Symbol Lookup'!$A$2:$C$4000,3,FALSE)"
End With

See if that doesn't stop the browse for file thing from happening.

The #NA error with the VLOOKUP() - wrap it in an IF() statement like this:

=IF(ISNA(VLOOKUP(.,.,.,.)),"do this if is #NA",VLOOKUP(.,.,.,.))

You can have your other VLOOKUP() formula where I put in "do this if is
#NA". The last VLOOKUP(.,.,.,.) in the formula is the very same one you
tested with the ISNA() function.

To find last used row in column C, one of these will work to give you the
address (in the current workbook, on the currently active sheet) :
lastRowAddress = RANGE("C" & Rows.Count).End(xlUp).Address
or if you're in Excel 2007:
lastRowAddress = RANGE("C" & Rows.CountLarge).End(xlUp).Address

You could get a variable to hold the addresses as a string like this:
someRangeAddress = "$A$2:" & Range("C" & Rows.Count).End(xlUp).Address

But I don't believe you're going to be able to use that in any way in your
VLOOKUP formula - for same reason your long string for the path name didn't
work. Frankly, since I've not tried (or don't remember ever trying) I'm not
even certain that would work across workbooks if the other is closed.

You'd probably be much better off giving the lookup range in that other
workbook a name which would adjust automatically as you insert/delete rows
into it and use the name instead of addresses. Note that in setting that
kind of thing up, you have to insert rows into the named range rather than
just adding a new entry to the end of it on an empty row - the new entry
won't be included in the range. You can include an empty row in it with a
note to insert new rows above that for inclusion in the VLOOKUP that uses a
named range.

In a macro in Excel 2002 I have this:

With DestSh
.Range("F2", Cells(LastR, 6)).Value = _
"=VLOOKUP(OFFSET(E$1,ROW()-1,0),'E:\Documents\Records\Finance\Securities\Research\[Symbol Lookup.xls]Symbol Lookup'!$A$2:$C$4000,3,FALSE)"
End With

It works. It populates column F with that formula, and the formula
works to look up stock names from the symbols in column E. This
works even though the lookup workbook is closed. However, the
macro pulls up a Windows Explorer Window set to that directory.
The workbook I want is highlighted, but Excel is waiting for me
to click on the book or hit Enter. Then the macro finishes.

I don't know why Excel is asking me for confirmation. I'd like it
to just work without a pause. Am I going about this the right way?
How can I stop the required interaction from me?

Also, I tried to set a string var in my macro to the long name so
I could use the var instead in the statement, but it doesn't work.
I can basically understand why it won't work, but I'd like to
know a reasonable approach. Oh! It dawns on me that I could
set a name in the worksheet and just use that in the macro. Hmmmmm.

Second question about this: some of the lookups fail with an #NA
error. I expected that. In the case of these errors, I want to
set those cells to a different VLOOKUP statement instead. I
don't know how to get my macro to do such a thing. (The second
VLOOKUP would be similar, but would start in column B instead of A.)

I'd also like to know how I can find the last row in my lookup
table. There are actually 2900-something entries in it now.
I don't need to say $A$2:$C$4000, but would rather say
$A$2 to lastrow in column C. Any ideas here would also
be most appreciated.

dman

 

[Dallman Ross]

Back on 2 July 2007 in

I'll tackle 2 pieces of your puzzle:

Sir, I am embarrased to see belatedly that this from you remained
unanswered. I do apologize. I wrote an answer and thought I
posted it. Now I'm reviewing the thread and find my answer is not here.
I don't know what happened, but I presume I left my answer in a drafts
folder. I've been traveling, etc., etc.

In any case, this was all extremely helpful indeed, and I am
most grateful. Let me review developments since this posting
of yours:

Ok, 2 1/2: In your code don't use .Value =, use .Formula =, you're setting
up a formula, not a value. So...
With DestSh
.Range("F2", Cells(LastR, 6)).Formula = _

"=VLOOKUP(OFFSET(E$1,ROW()-1,0),'E:\Documents\Records\Finance\Securities\Research\[Symbol Lookup.xls]Symbol Lookup'!$A$2:$C$4000,3,FALSE)"
End With
Excellent.

See if that doesn't stop the browse for file thing from happening.

It turns out the browse-for-file problem was my own doing, in that the
path to the lookup table had gotten corrupted and I had not noticed.
See that "E:\Documents\..." up there? That should have been "E:\My Documents\..."
Once I fixed that, all was well. Still, I didn't think that long formula was
very handsome in my VBA, so I worked at it some more. Now it looks like this:

For myRow = lastRow(DestSh) To 2 Step -1
' delete rows with "Canceled" in Col A
If .Cells(myRow, 1).Value = "Canceled" Then
.Rows(myRow).Delete

ElseIf Len(Cells(myRow, 5)) > 4 Then
' add option formula
Cells(myRow, 6).Formula = _
"=optionMonth&optionType&VLOOKUP(optionRoot,optionTable,inCoNames,FALSE)"

Else
' add stock formula
Cells(myRow, 6).Formula = _
"=VLOOKUP(Symbol,stockTable,inCoNames,FALSE)"

End If
Next

The "optionTable" and the "stockTable" are actually the same
workbook/worksheet. But the ranges are different. The optionTable
starts with Column B; the stockTable starts with Column A. the
"inCoNames" named range is the same column as well, but there is an
If-statement in the name such that if we're looking up an option
it returns "2", but if we're looking up a stock it returns "3". I
find it cleaner to have the field named instead of just using some
inexplicable number here.

The #NA error with the VLOOKUP() - wrap it in an IF() statement like this:

=IF(ISNA(VLOOKUP(.,.,.,.)),"do this if is #NA",VLOOKUP(.,.,.,.))

You can have your other VLOOKUP() formula where I put in "do this if is
#NA". The last VLOOKUP(.,.,.,.) in the formula is the very same one you
tested with the ISNA() function.

That was also helpful, and I've added the knowledge to my pool.

To find last used row in column C, one of these will work to give you the
address (in the current workbook, on the currently active sheet) :
lastRowAddress = RANGE("C" & Rows.Count).End(xlUp).Address
or if you're in Excel 2007:
lastRowAddress = RANGE("C" & Rows.CountLarge).End(xlUp).Address

Good tips!

You could get a variable to hold the addresses as a string like this:
someRangeAddress = "$A$2:" & Range("C" & Rows.Count).End(xlUp).Address

But I don't believe you're going to be able to use that in any way in your
VLOOKUP formula - for same reason your long string for the path name didn't
work. Frankly, since I've not tried (or don't remember ever trying) I'm not
even certain that would work across workbooks if the other is closed.

Yeah, I've more or less given up on that for now and am just using 4000
as the last row number in the foreign workbook. That gives me about 34%
"breathing room" and is plenty conservative enough.

Muchas gracias, Mr. Latham!

Kind regards,
Dallman Ross

==========================================

You'd probably be much better off giving the lookup range in that other
workbook a name which would adjust automatically as you insert/delete rows
into it and use the name instead of addresses. Note that in setting that
kind of thing up, you have to insert rows into the named range rather than
just adding a new entry to the end of it on an empty row - the new entry
won't be included in the range. You can include an empty row in it with a
note to insert new rows above that for inclusion in the VLOOKUP that uses a
named range.

In a macro in Excel 2002 I have this:

With DestSh
.Range("F2", Cells(LastR, 6)).Value = _
"=VLOOKUP(OFFSET(E$1,ROW()-1,0),'E:\Documents\Records\Finance\Securities\Research\[Symbol Lookup.xls]Symbol Lookup'!$A$2:$C$4000,3,FALSE)"
End With

It works. It populates column F with that formula, and the formula
works to look up stock names from the symbols in column E. This
works even though the lookup workbook is closed. However, the
macro pulls up a Windows Explorer Window set to that directory.
The workbook I want is highlighted, but Excel is waiting for me
to click on the book or hit Enter. Then the macro finishes.

I don't know why Excel is asking me for confirmation. I'd like it
to just work without a pause. Am I going about this the right way?
How can I stop the required interaction from me?

Also, I tried to set a string var in my macro to the long name so
I could use the var instead in the statement, but it doesn't work.
I can basically understand why it won't work, but I'd like to
know a reasonable approach. Oh! It dawns on me that I could
set a name in the worksheet and just use that in the macro. Hmmmmm.

Second question about this: some of the lookups fail with an #NA
error. I expected that. In the case of these errors, I want to
set those cells to a different VLOOKUP statement instead. I
don't know how to get my macro to do such a thing. (The second
VLOOKUP would be similar, but would start in column B instead of A.)

I'd also like to know how I can find the last row in my lookup
table. There are actually 2900-something entries in it now.
I don't need to say $A$2:$C$4000, but would rather say
$A$2 to lastrow in column C. Any ideas here would also
be most appreciated.

dman

 

[JLatham]

You're welcome. Glad I could add to your knowledge pool.

Back on 2 July 2007 in

I'll tackle 2 pieces of your puzzle:

Sir, I am embarrased to see belatedly that this from you remained
unanswered. I do apologize. I wrote an answer and thought I
posted it. Now I'm reviewing the thread and find my answer is not here.
I don't know what happened, but I presume I left my answer in a drafts
folder. I've been traveling, etc., etc.

In any case, this was all extremely helpful indeed, and I am
most grateful. Let me review developments since this posting
of yours:

Ok, 2 1/2: In your code don't use .Value =, use .Formula =, you're setting
up a formula, not a value. So...
With DestSh
.Range("F2", Cells(LastR, 6)).Formula = _

"=VLOOKUP(OFFSET(E$1,ROW()-1,0),'E:\Documents\Records\Finance\Securities\Research\[Symbol Lookup.xls]Symbol Lookup'!$A$2:$C$4000,3,FALSE)"
End With
Excellent.

See if that doesn't stop the browse for file thing from happening.

It turns out the browse-for-file problem was my own doing, in that the
path to the lookup table had gotten corrupted and I had not noticed.
See that "E:\Documents\..." up there? That should have been "E:\My Documents\..."
Once I fixed that, all was well. Still, I didn't think that long formula was
very handsome in my VBA, so I worked at it some more. Now it looks like this:

For myRow = lastRow(DestSh) To 2 Step -1
' delete rows with "Canceled" in Col A
If .Cells(myRow, 1).Value = "Canceled" Then
.Rows(myRow).Delete

ElseIf Len(Cells(myRow, 5)) > 4 Then
' add option formula
Cells(myRow, 6).Formula = _
"=optionMonth&optionType&VLOOKUP(optionRoot,optionTable,inCoNames,FALSE)"

Else
' add stock formula
Cells(myRow, 6).Formula = _
"=VLOOKUP(Symbol,stockTable,inCoNames,FALSE)"

End If
Next

The "optionTable" and the "stockTable" are actually the same
workbook/worksheet. But the ranges are different. The optionTable
starts with Column B; the stockTable starts with Column A. the
"inCoNames" named range is the same column as well, but there is an
If-statement in the name such that if we're looking up an option
it returns "2", but if we're looking up a stock it returns "3". I
find it cleaner to have the field named instead of just using some
inexplicable number here.

The #NA error with the VLOOKUP() - wrap it in an IF() statement like this:

=IF(ISNA(VLOOKUP(.,.,.,.)),"do this if is #NA",VLOOKUP(.,.,.,.))

You can have your other VLOOKUP() formula where I put in "do this if is
#NA". The last VLOOKUP(.,.,.,.) in the formula is the very same one you
tested with the ISNA() function.

That was also helpful, and I've added the knowledge to my pool.

To find last used row in column C, one of these will work to give you the
address (in the current workbook, on the currently active sheet) :
lastRowAddress = RANGE("C" & Rows.Count).End(xlUp).Address
or if you're in Excel 2007:
lastRowAddress = RANGE("C" & Rows.CountLarge).End(xlUp).Address

Good tips!

You could get a variable to hold the addresses as a string like this:
someRangeAddress = "$A$2:" & Range("C" & Rows.Count).End(xlUp).Address

But I don't believe you're going to be able to use that in any way in your
VLOOKUP formula - for same reason your long string for the path name didn't
work. Frankly, since I've not tried (or don't remember ever trying) I'm not
even certain that would work across workbooks if the other is closed.

Yeah, I've more or less given up on that for now and am just using 4000
as the last row number in the foreign workbook. That gives me about 34%
"breathing room" and is plenty conservative enough.

Muchas gracias, Mr. Latham!

Kind regards,
Dallman Ross

==========================================

You'd probably be much better off giving the lookup range in that other
workbook a name which would adjust automatically as you insert/delete rows
into it and use the name instead of addresses. Note that in setting that
kind of thing up, you have to insert rows into the named range rather than
just adding a new entry to the end of it on an empty row - the new entry
won't be included in the range. You can include an empty row in it with a
note to insert new rows above that for inclusion in the VLOOKUP that uses a
named range.

In a macro in Excel 2002 I have this:

With DestSh
.Range("F2", Cells(LastR, 6)).Value = _
"=VLOOKUP(OFFSET(E$1,ROW()-1,0),'E:\Documents\Records\Finance\Securities\Research\[Symbol Lookup.xls]Symbol Lookup'!$A$2:$C$4000,3,FALSE)"
End With

It works. It populates column F with that formula, and the formula
works to look up stock names from the symbols in column E. This
works even though the lookup workbook is closed. However, the
macro pulls up a Windows Explorer Window set to that directory.
The workbook I want is highlighted, but Excel is waiting for me
to click on the book or hit Enter. Then the macro finishes.

I don't know why Excel is asking me for confirmation. I'd like it
to just work without a pause. Am I going about this the right way?
How can I stop the required interaction from me?

Also, I tried to set a string var in my macro to the long name so
I could use the var instead in the statement, but it doesn't work.
I can basically understand why it won't work, but I'd like to
know a reasonable approach. Oh! It dawns on me that I could
set a name in the worksheet and just use that in the macro. Hmmmmm.

Second question about this: some of the lookups fail with an #NA
error. I expected that. In the case of these errors, I want to
set those cells to a different VLOOKUP statement instead. I
don't know how to get my macro to do such a thing. (The second
VLOOKUP would be similar, but would start in column B instead of A.)

I'd also like to know how I can find the last row in my lookup
table. There are actually 2900-something entries in it now.
I don't need to say $A$2:$C$4000, but would rather say
$A$2 to lastrow in column C. Any ideas here would also
be most appreciated.

dman