# Posts by jumper5two

• ## Locate any consecutive cells in a row (where employee worked)

Thanks for taking your time to read. I would love to have this working, it would help so much! (1 Excel file attached)

When scheduling, I would like a person's workdays to highlight (turn red with a yellow stripe) if they are accidentally scheduled 7 or more days in a row in any of the 4-week (28 day period).

So it should highlight and let the user know if someone doesn't have at least 1 day off duty in any 7 consecutive days.

Off days can be any of the following four: Blank-not scheduled, Off - Off duty, V - Vacation, X - Not allowed to work

My conditional formatting code below says "If the number of days off (Blanks, Offs, V's, or Xs) in that 7 day range = 0, then highlight because that person would be overworked." The code below actually seems to work in highlighting the first day of any 7 day ranges, but not the entire set of 7 in a row days.

Code
1. =COUNTBLANK(C6:XDJ6)+COUNTIF(C6:XDJ6,"=Off")+COUNTIF(C6:XDJ6,"=V")+COUNTIF(C6:XDJ6,"=X")=0

The 2nd bonus question for this formula (well bonus for me) is that we have 13 sheets of these 28 day schedules that make up the year. So I really also have to check that the days at the beginning and at the end of this schedule also don't go into 7 days when connecting to the prior and next schedule on other sheets. Best way to do that?? Would be easy if they were one big spreadsheet but...

Thank you,

David

• ## Sequentially filling fuel tanks using VBA

Re: Sequentially filling fuel tanks using VBA

"Center across selection" ahhhhhhhhh!

Thanks again,
David

• ## Sequentially filling fuel tanks using VBA

Re: Sequentially filling fuel tanks using VBA

[COLOR="red"]Thanks to all [/COLOR]who read my VBA problem post. Especially thank you [COLOR="red"]Stefan G[/COLOR] for your quick response with the named ranges and the what if's (30000 error). Oh, my mistake on the E18.. should be E17

Thank you [COLOR="red"]RoyUK[/COLOR] for going back and checking "old" posts with only a few replies. Your code worked great with the "Case constructs". I have just added StefanG's idea to it to clear the unused cells with "0's".

My question is: why on the 2nd "Case is" do you have a [COLOR="Blue"]".value"[/COLOR] after that range but not on the others? What difference does the ".value" make? Oh, the reason I used ranges is that I have merged the cells and the company Excel '97 doesn't seem to accept just the first cell in the merged range.

[COLOR="Red"]David[/COLOR]

• ## Sequentially filling fuel tanks using VBA

[COLOR="red"]Hello all,[/COLOR]

I don't really know VBA but I am trying to get the following code to work. The instructions of at least one (but only one) of the following "IF" statements should always be run. It runs but does not produce correct results. I believe my logic is correct but the proper code is lacking! I have attached an easy to follow worksheet of what it is for if that helps/is needed.
Thanks very much folks,
[COLOR="Red"]David[/COLOR]

• ## Named Formula keeps changing

Re: Named Formula keeps changing

I see!!

I had read that there was a 256 limit on what you actually named the range/formula but didn't know about the 256 limit to the formula part itself. I guess I figured since a cell"s formula limit was 1024 then so would a named formula's limit be 1024 characters as well. I guess never assume.

The named formula appears to be working fine for now - even though it appears to be incomplete in the "refers to" box. I will just be happy it was designed to work anyway.

Thanks for the info,
Best, David

• ## Named Formula keeps changing

Re: Named Formula keeps changing

Very interesting... thank you Dave.

Has anybody else out there at least had that truncating/chopping of the formula after going back to look at it???

Perhaps truncating is normal for somewhat long formulas when defining a named formula?

Best,
David

• ## Named Formula keeps changing

Morning Folks (depending where you are),

I have named a long formula so it is easier to use in other formulas. In defining it I simply paste it into the "refers to" block. The strange thing is that after I click "ok" and then come back to it, it has changed. It adds the sheet name to each cell ref (which I think is normal) but leaves out a large portion of the formula (truncated after X18). The second strange thing is that it usually works just fine. But sometimes I will open the workbook and it is displaying a "#Null!".

Does anybody know what's going on so I have a chance of sleeping in the next 24 hours????
Regards,
David
Code that is pasted into the "refers to" box:

Code
1. =((\$S\$14*\$V\$14*\$X\$14*\$I\$14+\$S\$16*\$V\$16*\$X\$16*\$I\$16+\$S\$18*\$V\$18*\$X\$18*\$I\$18)/1728/1230*420/2+116.5)*\$U\$6

Code that is there after clicking "OK" and then returning to look at it again:

Code
• ## Finding total freight weight in different compartments

Re: Finding total freight weight in different compartments

Hey Chris,

You are awesome! Thanks a whole bunch for putting that together. It's folks like you that keep this place going. It would have taken me months to figure that out!

The spinner changer works really neat. It took me a while to figure out what you had done but I learned a lot. I also did like you suggested and figured the numbers manually. I came up with some different numbers so I tweaked here and there till they matched exactly. Once I was finally able to grasp how you changed the starting bin, I saw another way to do it as well. I changed the starting bin location at N5 depending on what bin was selected. It seems to work but if you or anybody would want to give it a run through to see if it trips up I'd value your opinion.

Hey thanks again,
David

• ## Finding total freight weight in different compartments

Combining two lists into one, sorted, output to different column.

Hi Chris... Thanks for the reply and spreadsheet you posted!!
It really showed me a way to get this started! This has really turned into a "mind-bender" as you are really the only person to see a way to do it.
Now I understand what you mean by "Automating the ordered list". I tweaked just a bid on some of the formulas but the list is a problem. Any more ideas how to automate that list. I'll bet the whole thing might be a lot simpler with some VBA but I'm still pretty basic with code.

For anyone else basically somehow I need to take the list: Sheet 2!I8:I15 and list those items in Sheet 1!A2:A9. Then take the list: Sheet 2!D8:D27 and list in Sheet 1!A10:A29. Then sort ascending. Or if you see another angle to approach this whole thing?

Everyone... thanks for taking a look.
David
Attachment w/ two sheets

• ## Finding total freight weight in different compartments

Thanks for Reading, Everyone.

Hopefully my attachment will help clarify.

My problem: Imagine a semi-trailer truck in this instance. I need to know the total combined weight of the freight for each individual compartment. The trailer is divided up into bins C at the very front through J at the rear. These bins (C -> J) are different lengths, as are the freight bundles/boxes when they arrive. So the boxes are loaded as they arrive from front to rear with no space inbetween. The bins are not physically divided so boxes from one compartment will overhang into the next.

Example: Box 1 is long enough to fill up bin C,D, & 1/2 of E. Box 2 is short so it only fills up to the 3/4 mark of E. Box 3 fills up the rest of E and fills 1/3 of F. I'd like to plan for at least 20 boxes and give the loader the option of where to start loading the freight (If there are only two boxes he/she would probably start loading them in bins F.)

I figured I would need to first find each freight bundle's weight per inch... but have now realized this gets real complicated fast. I'm in over my head.

Best Thanks to anybody who can help,
David

• ## "Can't find project or library" Error

Re: &quot;Can't find project or library&quot; Error

Hi,

Are you asking if the two add-ins you listed are loaded on the bad computer?

I don't know much about add-ins but I do know that those two you listed are NOT checked in >References. But I DO see them listed in the Project Explorer window along with my workbook. They are NOT listed or running on my "good" computer at home though.

Is that what you needed to know??

Best,
David

• ## "Can't find project or library" Error

Re: &quot;Can't find project or library&quot; Error

Sorry - copied the wrong code. the correct VBA is below with the debug line in RED:

Code
1. Private Sub Worksheet_Change(ByVal Target As Range)
2. If Intersect(Target, Range("AI5:AM5,AP5:AT5")) Is Nothing Then Exit Sub
3. Application.EnableEvents = False
4. [COLOR=Red][B]Target = UCase(Target)[/B] [/COLOR]
5. Application.EnableEvents = True
6. End Sub

I've attached a copy of the file in the post PRIOR to this one.
Good Idea Jack!

Thanks again,
David

• ## "Can't find project or library" Error

Re: &quot;Can't find project or library&quot; Error

Great news!!.....but...

I unchecked the MS Calendar reference in the version at home. Saved it and took to office to run. No more "Can't find project or library" error. And the missing reference is gone!! However, now I get "Error 13 - type mismatch" when the macro is run. The macro purpose is to automatically change all inputs to upper case. And wouldn't you know, it works fine on the home computer :rambo: . The help files give about a hundred reasons for this error to show up :? Debug highlights the line below.

Code
1. Private Sub Worksheet_Change(ByVal Target As Range)
2. If Intersect(Target, Range("AI5:AM5,AP5:AT5,AP4:AT4")) Is Nothing Then Exit Sub
3. Application.EnableEvents = False
4. [B]Target = UCase(Target) [/B]
5. Application.EnableEvents = True
6. End Sub

## Files

• Macro.zip

(41.81 kB, downloaded 58 times, last: )
• ## Slow Macro

Re: Slow Macro

Filo65 thanks for responding.

I guess I don't understand the "Reset to Automatic Calculation" that you are talking about. Could you explain further please?

Note: When macro runs I don't get any screen flickering or obvious redrawing at all (and that's without using calc & screen-update = false).

Best,
David

• ## Slow Macro

Re: Slow Macro..........not solved??

Thanks everybody for reading. H1H I tried your stop calculating and screen-updating suggestions but they had no effect. I really thought that would be the fix. Maybe it still is... see below. Thanks

Bill I really appreciate your taking the time to ask if problem was solved (was out of town). I've attached a copy of my program in a very "chopped" state to meet the 45 kb limit.

After I chopped it I realized that the macro ran instantaneously. So I chopped it again, piece at a time to see what was the slow-down. Turns out there is no ONE blockage. Every time I deleted some graphics object, formulas, or formatting (on just the sheet the macro refered to) it ran a little bit faster (I timed with a stopwatch).

So I'm wondering why the screen-updating & calculation = false didn't speed it up?

Any revelations (the password is ozgrid)???
Thanks David

• ## "Can't find project or library" Error

Re: &quot;Can't find project or library&quot; Error

Thanks for responding Ivan & anyone who reads this. When I try to uncheck the checked box that says missing, I get the message "Can't remove control or reference: in use". In answer to your question "What file is missing?" I don't know because the checked box only says "Missing:", nothing else.

I've found that I get this error on two office computers but it works fine on the other one (they all have Win98se and xl'97). So I compare the >reference dialog boxes. The difference (besides the "Missing:" line) is the one that works at the office and the one at home have a checked box labeled "Microsoft Calendar Control 8.0". I noted that this line is not even available to be checked on the "bad" computers. I have no idea what calendar control is used for. I do use the "Today()" function in my sheet but would that affect it?

Another big thing is that on the "bad" computers I noticed 2 projects called "atpvbaen.xls and funcres". They are not "checked" in >references and I don't know how come they are there.

I'm a bit long-winded, just wanted to help as much as possible,
Thanks again,
David

• ## "Can't find project or library" Error

Wondered if someone or several could lend a hand?It works find at home on Win98se Excel '03 but I get Compile Error "Can't find project or library" when the following code is run at the office on Win98se Excel '97.

' Forces input to uppercase.

Code
1. Private Sub Worksheet_Change(ByVal Target As Range)
2. If Intersect(Target, Range("AI5:AM5,AP5:AT5,AP4:AT4")) Is Nothing Then Exit Sub
3. Application.EnableEvents = False
4. Target = UCase(Target)
5. Application.EnableEvents = True
6. End Sub

It yellow-highlights the first line and grey high-lights the word "UCase".

After reviewing previous posts on this subject I brought up the >Reference dialog box and one of the checked boxes said "Missing:"
It's my understanding that usually the "Missing:" has a name associated with it.
I bring the program from home on a memory stick, run the program, and then >save as with the same name but to a new location.
The bottom of the dialog box (when I click on missing) says:
Location: (has the address where I stored it on the new computer)
Language: Standard

I have this code on two of the sheets and they both have the same problem.
Most Humbled,
David

• ## Easy Formula Question

Re: Easy Formula Question

5 groups multiplied by 2 different tests multiplied by 4 possible grades = a really complex formula. Especially since the grade ranges overlap - ie: 100 is a perfect score on one test but only 33% on the other. Changing the scores to percents would help simplify the process.
You could also simplify by adding additional columns with the scores converted to letter grades in those columns.

You might try the "Dcount" function. I believe it's set up for just this type of database criteria searching.

A copy of your file completed thus far would probably really help.

Best of luck,
David

• ## Slow Macro

Re: Slow Macro

AN14 and N14 contain formulas. I was trying to remove their formulas but have the values remain (have the output of the formulas remain, without the formula themselves)(copy then paste special>values back into the same cell). Sorry if it's a real mess. I am self taught (hard to tell huh?).

I am using Win 98se and Excel 2003.

I am not sure how to post my file because it is way over the 45kb (even zipped).

Does this help or do I need to find a way to post or clarify better??

Thanks for helping (esp on a Fri night),
David

• ## Slow Macro

Wondered if ya'all could give me some suggestions to turbo-charge my macro. It takes over a second to run but of course seems like 10 minutes. I don't really understand "with" statements and wonder if the problem lurks therein. Basically when a user hits a button, it locks the single item weight (AN14) to whatever the value is at that moment. It then shows a spinner to input the quantity (I14). Then as the quantity changes, the total weight (N14) changes automatically with it using that single item weight for reference. In "normal mode" the user can input quantity and total weight - then the weight per item is automatically calculated. I'd appreciate anyone pointing out any.. and or all bad programming in addition to what might be gumming up the works.
Thanks a lot,
David