Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
ADO call to lookup table
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\Rese arch\[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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
ADO call to lookup table
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\Rese arch\[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. "Dallman Ross" wrote: 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\Rese arch\[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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
ADO call to lookup table
Back on 2 July 2007 in
, JLatham spake thusly: 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\Rese arch\[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,option Table,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. "Dallman Ross" wrote: 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\Rese arch\[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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
ADO call to lookup table
You're welcome. Glad I could add to your knowledge pool.
"Dallman Ross" wrote: Back on 2 July 2007 in , JLatham spake thusly: 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\Rese arch\[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,option Table,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. "Dallman Ross" wrote: 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\Rese arch\[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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Call Center Management: How to calculate 'cost per call' | Excel Discussion (Misc queries) | |||
Lookup Table help | Excel Discussion (Misc queries) | |||
Excel ? Do Not Call Table | Excel Worksheet Functions | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) | |||
lookup a value on a table | Excel Worksheet Functions |