more vlookup help please

[sasoderl]

Ok, I have some code written that uses vlookup. Now, I am having a type conversion problem. To take care of text to numbers, I have:

=VLOOKUP(--A1, data, 2, false)

But I also have text in my "data" and using this VLOOKUP gives the error:

#VALUE!

Now, if I switch back to

=VLOOKUP(A1, ...

I get the #NA error in the cells that need to lookup a number and the correct values in the cells that lookup text.

I have numbers and text sprawled throughout the column A in the workbook. So, I can't run one down so many rows and then run the other.

Any help would be greatly appreciated

 

[just_jon]

On second thought ... does this work --

=IF(AND(LEN(A2),ISNUMBER(--A2)),VLOOKUP(--A2, [DATA.xls]Sheet1!$A$1:$D$63, 2, FALSE),IF(LEN(A2),VLOOKUP(A2, [DATA.xls]Sheet1!$A$1:$D$63, 2, FALSE),""))

Change all the data in DATA to a General format. Change the ,2, bit in the formula to ,3, and ,4, for the 2 other formulas.

If you want, the macro could detect your end of data, bypassing the need for an input box.

 

[just_jon]

On second thought ... does this work --

=IF(AND(LEN(A2),ISNUMBER(--A2)),VLOOKUP(--A2, [DATA.xls]Sheet1!$A$1:$D$63, 2, FALSE),IF(LEN(A2),VLOOKUP(A2, [DATA.xls]Sheet1!$A$1:$D$63, 2, FALSE),""))

Change all the data in DATA to a General format. Change the ,2, bit in the formula to ,3, and ,4, for the 2 other formulas.

If you want, the macro could detect your end of data, bypassing the need for an input box.

 

[sasoderl]

I would love my macro to detect the end of the data and bypass the need for the text box, can you let me know? Also, in my worksheet, I have a row with years. I want to be able to run a query that returns the total of $'s where year = whatever year user wants to see total for. Can you possibly help with that as well?

 

[sasoderl]

Also, I get a runtime error when using this:

ActiveCell.Formula = IF(AND(LEN(B2),ISNUMBER(--B2)),VLOOKUP(--B2, [DATA.xls]Sheet1!$A$1:$D$63, 2, FALSE),IF(LEN(B2),VLOOKUP(B2, [DATA.xls]Sheet1!$A$1:$D$63, 2, FALSE),""))

 

[just_jon]

OK - For end-of-data. On your Task_Table1 sheet of filr example.xls, column A is the key column - if it has data in it, then we're interested. The below lined will give that to you.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> SubmissionFeeCategoryMacro()

<SPAN style="color:#00007F">Dim</SPAN> LastRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
LastRow = ThisWorkbook.Worksheets("Task_Table1").[A65536].End(xlUp).Row

Range("F2").Select
ActiveCell.Formula = "=IF(AND(LEN(A2),ISNUMBER(--A2)),VLOOKUP(--A2, [data2.xls]Sheet1!$A$1:$D$63, 2, FALSE),IF(LEN(A2),VLOOKUP(A2, [data2.xls]Sheet1!$A$1:$D$63, 2, FALSE),""""))"

Range("G2").Select
ActiveCell.Formula = "=IF(AND(LEN(A2),ISNUMBER(--A2)),VLOOKUP(--A2, [data2.xls]Sheet1!$A$1:$D$63, 3, FALSE),IF(LEN(A2),VLOOKUP(A2, [data2.xls]Sheet1!$A$1:$D$63, 3, FALSE),""""))"

Range("H2").Select
ActiveCell.Formula = "=IF(AND(LEN(A2),ISNUMBER(--A2)),VLOOKUP(--A2, [data2.xls]Sheet1!$A$1:$D$63, 4, FALSE),IF(LEN(A2),VLOOKUP(A2, [data2.xls]Sheet1!$A$1:$D$63, 4, FALSE),""""))"

Range("F2:H2").AutoFill Destination:=Range("F2:H" & LastRow), Type:=xlFillDefault

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Change data2 refs to DATA

 

[just_jon]

I would love my macro to detect the end of the data and bypass the need for the text box, can you let me know? Also, in my worksheet, I have a row with years. I want to be able to run a query that returns the total of $'s where year = whatever year user wants to see total for. Can you possibly help with that as well?

Where in DATA are the years?

 

[just_jon]

Also, I get a runtime error when using this:

ActiveCell.Formula = IF(AND(LEN(B2),ISNUMBER(--B2)),VLOOKUP(--B2, [DATA.xls]Sheet1!$A$1:$D$63, 2, FALSE),IF(LEN(B2),VLOOKUP(B2, [DATA.xls]Sheet1!$A$1:$D$63, 2, FALSE),""))

That's because I forgot to tell you to double-up those quotes. See above code.

 

[sasoderl]

Actually, the years are not in data at all. They are in the worksheet. (I have been just sending you the lookup column) However, there is a column of years in my worksheet and a column of $ amounts (which comes from this VLOOKUP macro) Does this help?

 

[just_jon]

Can you send the test wb's, with the headings where they would be in the bigger file?

 

[just_jon]

How do you match just the year of interest in the DATA array? If I decide I'm only interested in 2003, how do I tell which rows in DATA are from that year as opposed to other years?

Also, there are a few more fields -- which are lookups, and from which columns in DATA?