Posts by jjst34

    Re: complex lookup vba solution


    Hi Derk,,
    several weeks ago you helped me out w/ this code.. It is working awesome! I confired all the #'s.. and they were off only about $200 on a $90 million report.
    I figured out the small problem and its kind of weird.. when the code pulls the figures over, and reads the percentages, for some reason if the percentage is 67.5 it rounds up to 68 and there were some that were 33.34 and it rounded down to 33. There are others like the 66.66 that don't round and work right.. why is it that only some would round and others dont? clearly I don't want any rounding.. any ideas? Thanks!

    Re: formula only works when all workbooks are open??


    The only problem is,, I have 62 of them w/ different formula's. that's a lot of copy/pasting all the time.. though a good idea.. but hoping to get another fix? I have some workbooks that have external links and I don't have to have everything open for it to show the values?!!? I'm just not sure why this one doesn't. maybe it's the sumif specifically? I dunno!

    is there a way around this? I have some formulas, all sumif's, that work fine,, but only when all the workbooks in the formula are open. When I open just the workbook w/ the formulas in it, regardless if I choose to update external links or not, I get a #VALUE! error. As soon as I open them, the correct values appear.
    The book w/ the formulas in it will be given to other users as read-only and they don't have access to open the other workbooks... How do I get around this? I also want the formulas to stay because every so often I will have to change the values. Thanks!

    Re: sumif - criteria as a range


    apparently you didn't understand the question..
    I want my criteria (the second argument)to be based on a range.. How can I go about this? I want it to sum if it's any of those #'s. Not just 1.


    and the help file does not tell me the answer to this question.. Thanks for the reply though..

    I have this as a formula in a cell:
    =SUMIF('[2005 Budbook.xls]01XA'!$B$11:$B$17,$K$4:$K$16,'[2005 Budbook.xls]01XA'!$L$11:$L$17)


    The criteria I want it to look up is each value in the cells K4:K16. My formula only sums based on the value in K4, it doesn't look in the rest of the range. How would I make this work correctly? Thanks a lot!

    I'd like to do a vlookup like this...
    first find "smith"
    then once smith is found look in the the range 4 columns over (column E) and a range of 10 rows down, and look for "cross", then return the value 1 column over from cross.
    Is this possible?


    basically smith will be found in say cell A324, then I want it to look in cells E324:E33 for "cross", lets say found in E329, then return the value in F329.


    Cross is not always in the same position in relation to smith, that's why I can't use an offset in the code. Thanks!

    i think i've figured it out.. i needed the statement after the first if.. so:


    If ActiveCell.Offset(8, -7) = "Steve" And ActiveCell.Offset(-1, -7) = "home" Then


    Range(ActiveCell(), ActiveCell.Offset(8, 0)).Select


    ElseIf ActiveCell.Offset(-1, -7) <> "100" And ActiveCell.Offset(8, -7) = "home" Then
    Range(ActiveCell(), ActiveCell.Offset(8, 0)).Select
    Else: Range(ActiveCell(), ActiveCell.Offset(7, 0)).Select
    End If


    guess that's all i needed was a break from it for a minute.. thanks anyways!

    I have 2 if statements that work fine separetly, but I can't get them to both work.. I need it basically say"
    if either one of the if statements is true, then blah blah..
    i tried using the else if statement but it doesn't seem to work just right.. is there any way to use an OR statment there? it won't let me, but i may be doing it wrong... THanks!


    If ActiveCell.Offset(8, -7) = "Steve" And ActiveCell.Offset(-1, -7) = "home" Then
    ElseIf ActiveCell.Offset(-1, -7) <> "100" And ActiveCell.Offset(8, -7) = "home" Then
    Range(ActiveCell(), ActiveCell.Offset(8, 0)).Select
    Else: Range(ActiveCell(), ActiveCell.Offset(7, 0)).Select
    End If




    so basically this works:
    If ActiveCell.Offset(8, -7) = "Steve" And ActiveCell.Offset(-1, -7) = "home" Then
    Range(ActiveCell(), ActiveCell.Offset(8, 0)).Select
    Else: Range(ActiveCell(), ActiveCell.Offset(7, 0)).Select
    End If



    this works:


    If ActiveCell.Offset(-1, -7) <> "100" And ActiveCell.Offset(8, -7) = "home" Then
    Range(ActiveCell(), ActiveCell.Offset(8, 0)).Select
    Else: Range(ActiveCell(), ActiveCell.Offset(7, 0)).Select
    End If


    but i can't get them to work together.. any help is appreciated. Thanks!

    I have signed up for the newsletter and will also take a look at the book you've recommended..


    I do have one more question regarding this project... I am getting an error on your code when I run it w/ the full report. It's on the "s = Cells(i, 1)" part. It's due to my formatting of the report that I do. Before I put in all of your code, I have a piece of code to format the report. It goes like this..


    Workbooks.OpenText FileName:= _
    "C:\Documents and Settings\jwetmore\Desktop\report.txt", Origin:=xlWindows, _
    StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(7, 1), Array(19 _
    , 1), Array(24, 1), Array(25, 1), Array(29, 1), Array(80, 1), Array(114, 1), Array(129, 1))
    Application.ScreenUpdating = False
    On Error GoTo done
    Do While 1 < 1000
    Cells(1, 8).EntireColumn.Find("Page", LookIn:=xlWhole, lookat:=xlWhole, matchcontrolcharacters:=False).Select
    Range(ActiveCell(), ActiveCell.Offset(7, 0)).Select
    Selection.EntireRow.Delete
    'a = 1 'this is just a bogus variable
    Loop
    done:




    ActiveWorkbook.SaveAs FileName:= _
    "C:\Documents and Settings\jwetmore\Desktop\report.xls", FileFormat:= _
    xlExcel9795, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
    False, CreateBackup:=False
    Range("A1").Select
    Application.ScreenUpdating = True
    End Sub



    now when that's all done, depending on the page breaks occasionally I end up w/ 2 rows like this:


    Percent Project
    Percent Project


    How would I run something that that does a find for "Percent" and if the row below it also says percent to delete the TOP one? I think this should solve the error i'm getting. Thanks!
    ps i'm working on it,, but i keep getting stuck in a loop! LOL I think it needs to be done from the bottom up also right? again thanks for the help!

    Derk... YOU ARE THE MAN!


    where on earth can I learn this stuff to the extent you have? Any specific books? Or is this classroom training? Self training? etc... this is wonderful! I appreciate it so much!

    Derk,,
    wondering if you can help me w/ another thing on this... The code u gave me worked excellent for what I needed.. Now I need something from the same report, but a little more complex, and I adapted your code for part of it.. but i'm stuck on something..
    I need to fill in the following columns on an excel spreadsheet..
    Percentage - Name - Fund - Org - Salary - Benefits - Total



    Now I can get everything done w/ your code from earlier, except for the percentage part because sometimes 100% of the salary is expensed to a particular fund, but on some there are several % splits, but the name is only listed once.. Anyways,, I know that explanation is garbage so I attached a spreadsheet showing a sampling of the report I'm extracting the info from.. And my desired outcome on sheet 2... If you could take a look at it and see what the possibilities are, I'd really appreciate it.

    Hi there,,
    I have a piece of code that is as follows:


    On Error GoTo done
    Do While 1 < 1000
    Cells(1, 8).EntireColumn.Find("Page", LookIn:=xlWhole, lookat:=xlWhole, ).Select
    Range(ActiveCell(), ActiveCell.Offset(7, 0)).Select
    Selection.EntireRow.Delete
    'a = 1 'this is just a bogus variable
    Loop
    done:


    it works fine,, except it will not find "Page 1" for instance.. How do I make it find any cell that has page in it regardless of what else is in the cell.. Kind of like unchecking that box "find entire cells only".


    Thanks a lot!