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

batman.. thanks your idea worked great... that control+shift+enter thing is pretty cool..

james.. enough said

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!

Re: Trying to pull a 0 using LEFT fuction

ok, sweet.. thanks that works great. Thank you!

Re: Trying to pull a 0 using LEFT fuction

well D2 is extracted from a text file which actually says 0100, but when it comes over to D2 it comes over as 100, so i have the whole row formated as a custom "0000"

ok. I have this formula in cell E2:

=+LEFT(D2,2)

D2 says 0100 and I want E2 to return 01, but it keeps ignoring the first 0 and returning 10. Any help is appreciated. Thanks!

I appreciate your help guys.. works great!

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!

Thanks again... It's now running all the way through... now time to confirm some #'s.. I really appreciate your help w/ this one!

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.

thank you!

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!

excellent.. I will do that.. Thanks Derk!