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

 

[sasoderl]

Ok, I guess I didn't explain very well. I start with worksheet with columns
A-E. I run macro to get F-H. So, I start with year (in column C) so year has nothing to do with DATA at all. After I run first macro to get F-H where the fee is in H, I want to either add up the fees that correspond to year 2004, 2005, etc OR I want the user to specify which year they would like to see a total for. Either way, this does not matter. They can just be given the total for each separate year, or they can be given a choice. Does this help?

 

[just_jon]

Ok, I guess I didn't explain very well. I start with worksheet with columns
A-E. I run macro to get F-H. So, I start with year (in column C) so year has nothing to do with DATA at all. After I run first macro to get F-H where the fee is in H, I want to either add up the fees that correspond to year 2004, 2005, etc OR I want the user to specify which year they would like to see a total for. Either way, this does not matter. They can just be given the total for each separate year, or they can be given a choice. Does this help?

Absolutely! Got a spot of real-life work, but will be back in a while...

 

[just_jon]

Where on the sheet would you like the totals to be placed? Do you know if the year column is text? How many years do we deal with? And, of either prompting for 1 or printing them all out. which do you prefer?

 

[sasoderl]

Hmm, I don't really have any ideas about where on the sheet I want the total to be placed. I don't think it really matters. Maybe in a new column.

The year column is number format

So far, we deal with years 2003, 2004, 2005, 2006, but this is subject to change. It will not go backwards, but may go forwards.

I think prompting for the year is what I prefer

 

[sasoderl]

You know, I finally got the variable "LastRow" to compile, but it doesn't work correctly??? However, if I use the text box prompt for row number, everything works. I have yet another question. Do you know of a way I can handle the cells in the "Task_Table1" sheet that are in the lookup column that are empty cells? I would prefer that they not fill in the 3 columns with #N/A. As of now, that is what they do.

By the way, I had to change it to:

Dim LastRow As Long
LastRow = ThisWorkbook.Worksheets("Sheet1").[A65536].End(xlUp).Row

in order for it to compile.

PS--Thanks for ALL of your help so far! You have saved me a ton of headaches!

 

[sasoderl]

ray: ray: ray: ray: ray: ray: ray: ray: ray: ray:

Yeah Jon! I got it to work! THANK YOU SOO MUCH! Whenever you have time, all that is left is to figure out this year thing, but my macro works BEAUTIFULLY!!!!

 

[just_jon]

Getting to that part; will post you a slightly revised macro as soon as I can fiure of where I'm bolloxing up the SUMIF ...

 

[just_jon]

See what this does --

<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>, YearOfInterest <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, SumOfYear <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Currency</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> Headers
Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> Xit
Headers = Array("Division", "Action", "Fee")

<SPAN style="color:#00007F">With</SPAN> Worksheets("Task_Table1")
    LastRow = .[B65536].End(xlUp).Row
    [F:H].ClearContents: .[I1:I2].ClearContents
    .[F2].Formula = "=IF(AND(LEN(TRIM(B2)),ISNUMBER(--B2)),VLOOKUP(--B2, [data2.xls]Sheet1!$A$1:$D$63, 2, FALSE),IF(LEN(TRIM(B2)),VLOOKUP(B2, [data2.xls]Sheet1!$A$1:$D$63, 2, FALSE),""""))"
    .[G2].Formula = "=IF(AND(LEN(TRIM(B2)),ISNUMBER(--B2)),VLOOKUP(--B2, [data2.xls]Sheet1!$A$1:$D$63, 3, FALSE),IF(LEN(TRIM(B2)),VLOOKUP(B2, [data2.xls]Sheet1!$A$1:$D$63, 3, FALSE),""""))"
    .[H2].Formula = "=IF(AND(LEN(TRIM(B2)),ISNUMBER(--B2)),VLOOKUP(--B2, [data2.xls]Sheet1!$A$1:$D$63, 4, FALSE),IF(LEN(TRIM(B2)),VLOOKUP(B2, [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">With</SPAN>

Xit:

Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>

<SPAN style="color:#00007F">If</SPAN> Err.Number <> 0 <SPAN style="color:#00007F">Then</SPAN>
    ErrMsg = "Error:" & Str(Err.Number) & " was generated " _
             & Err.Source & Chr(13) & Err.Description
    MsgBox ErrMsg, , "Error", Err.HelpFile, Err.HelpContext
    MsgBox "Processing Terminated."
    Err.Clear
    <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
<SPAN style="color:#00007F">Else</SPAN>
    YearOfInterest = Application.InputBox(prompt:="Enter your year of interest: ", Type:=1)
    <SPAN style="color:#00007F">With</SPAN> Worksheets("Task_Table1")
        .[F1:H1] = Headers
        .[I1] = YearOfInterest
        .[I2].Formula = "=SUMIF(C:C,""=""&" & YearOfInterest & ",H:H)"
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>

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

</FONT>

 

[sasoderl]

Yes! Yes! Yes!

Thank you very much for your help these last few days!

ray: ray: ray: ray: ray: ray: ray: ray:

 

[just_jon]

Well, let's see... it only took me ... uhmm... err... 25 posts to get it right.