Page 1 of 2
Excel 2007 - i made it disappear!
Posted: October 28 08, 11:33 am
by StatmanCrothers
haha!
i wrote a program that didn't just CRASH excel, it came up with a run-time error that shouldn't have been an error, so I clicked to save the file... and POOF, it was just gone. not even the "Report" or "Not Report" message ... it was just ... gone.
i wrote a VBA to do some automated processing of a very large text listing, and it was easiest to write if the modules all called each other recursively, without remorse or prudence, so it just keeps recursing deeper and deeper until the system gets confused.
i'll suppose i need to reprogram it to have actual exit points instead of a steadily deeper recursion. sounds like something i could take a look at down at the bar. is it past noon? where's my towel?
Re: Excel 2007 - i made it disappear!
Posted: October 28 08, 11:42 am
by Hungary Jack
Too smart for your own good.
But then again, it's a Microsoft product we are talking about.
Re: Excel 2007 - i made it disappear!
Posted: October 28 08, 12:35 pm
by KyCardinalFan
Is this a rant or a rave?
Re: Excel 2007 - i made it disappear!
Posted: October 28 08, 2:45 pm
by StatmanCrothers
KyCardinalFan wrote:Is this a rant or a rave?
i'm going to say its a rave. it makes me feel good to know that my merest whim can push the cutting edge software into the ether.
it's good to be ... unanticipated... by the powers that be.
Re: Excel 2007 - i made it disappear!
Posted: October 28 08, 2:56 pm
by StatmanCrothers
Something about this was tickling my brain and i just figured out what it was. At first I thought it reminded me of the smell of play-doh, but no, that was the napkins from BW3--use the standard brown recycled ones, not the play-doh-smelling ones embossed with the BW3 logo.
Does anyone remember "back in the day" (i think it was a thursday) when the Asteroids arcade game was all the rage .... for you younger kids, this was when you had to go to the arcade to play video games. It was a very high tech leap. The previous generation was your classic pinball machines.
In Asteroids, normally you shoot the original largest asteroids, they splits into two medium asteroids that fly off at angles, you shoot one of the mediums and they splits into two small asteroids that flys off at angles. you shoot the smallest asteroids and they go bye-bye. If you were fast on the trigger you could obliterate the entire sequence in one rain of fire.
As you advanced, each time a field was cleared, then even more would come out... up to a point... at the highest level of difficulty, there was a trick you could do because presumably the processor was overloaded. If you concentrated on making as many flying pieces as possible (not shooting the smallest pieces) but left one of the large ones, then once the computer was maxed out keeping track of so many flying objects, you could shoot the large one and it would just "poof" disappear, instead of splitting into two.
not unlike Excel 2007.
Re: Excel 2007 - i made it disappear!
Posted: October 28 08, 2:57 pm
by AWvsCBsteeeerike3
i didn't know vba was still widely used....
Re: Excel 2007 - i made it disappear!
Posted: October 28 08, 2:59 pm
by StatmanCrothers
AWvsCBsteeeerike3 wrote:i didn't know vba was still widely used....
its built into Microsoft Office apps. If you want to do something in the spreadsheet that's easier to do with programming than spreadsheet formulas, hit Alt-11 and you are in a VBA environment. The Excel spreadsheet is a built-in object model so you can do things spiffy.
Re: Excel 2007 - i made it disappear!
Posted: October 28 08, 3:35 pm
by Sutter'sBeard
Statman,
Since you appear to be an excel expert, I am trying to find a formula where a cell will automatically display a postage zone when I type the zip in another cell. In other words, if I type in a zip code in column a2, column b2 will display the correct postal zone from a chart/table that I have created. Can you give me any guidance on this please?
Re: Excel 2007 - i made it disappear!
Posted: October 28 08, 3:47 pm
by StatmanCrothers
Sutter'sBeard wrote:Statman,
Since you appear to be an excel expert, I am trying to find a formula where a cell will automatically display a postage zone when I type the zip in another cell. In other words, if I type in a zip code in column a2, column b2 will display the correct postal zone from a chart/table that I have created. Can you give me any guidance on this please?
Sure thats an easy one. You'd do it like this:
suppose on Sheet2 you have a table
Code: Select all
A B
1 ZIP postage zone
2 60103 Bartlett, IL
3 60104 Skokie, IL
4 ... ....
...
218 99999 Gooba, Guam
etc.
make sure the list is sorted in order by Zip Code.
now on Sheet1 let's say you have:
Code: Select all
A B C
1
2 Zip ? ______
3 Zone ______
4
you want to be able to type a zip code in cell B2 and have the postage zone come up in cell B3...
in B3 you would use the VLOOKUP( ) function... (vertical lookup)
=VLOOKUP( B2, Sheet2!A2:B218, 2 )
if you want to copy the formula to do the same thing for 2 different cells (to compare zip codes, maybe) you might want to use absolute reference like this
=VLOOKUP (B2, Sheet2!$A$2:$B$218, 2)
you can get fancier, but VLOOKUP is the easiest way to go.
if the list is incomplete or if you want to make sure you have a match, there is another flag you can add. the default i believe is that it finds the first match or if no match, first value greater than the match.
so if there was no 60103 in your list it might show the zone for the next entry, 60104 or whatever was next highest.
questions? clear as mud?
Re: Excel 2007 - i made it disappear!
Posted: October 28 08, 3:48 pm
by AWvsCBsteeeerike3
StatmanCrothers wrote:Sutter'sBeard wrote:Statman,
Since you appear to be an excel expert, I am trying to find a formula where a cell will automatically display a postage zone when I type the zip in another cell. In other words, if I type in a zip code in column a2, column b2 will display the correct postal zone from a chart/table that I have created. Can you give me any guidance on this please?
Sure thats an easy one. You'd do it like this:
suppose on Sheet2 you have a table
A B
1 ZIP postage zone
2 60103 Bartlett, IL
3 60104 Skokie, IL
4 ... ....
...
218 99999 Gooba, Guam
etc.
make sure the list is sorted in order by Zip Code.
now on Sheet1 let's say you have:
A B C
1
2 Zip ? ______
3 Zone ______
4
you want to be able to type a zip code in cell B2 and have the postage zone come up in cell B3...
in B3 you would use the VLOOKUP( ) function... (vertical lookup)
=VLOOKUP( B2, Sheet2!A2:B218, 2 )
if you want to copy the formula to do the same thing for 2 different cells (to compare zip codes, maybe) you might want to use absolute reference like this
=VLOOKUP (B2, Sheet2!$A$2:$B$218, 2)
you can get fancier, but VLOOKUP is the easiest way to go.
if the list is incomplete or if you want to make sure you have a match, there is another flag you can add. the default i believe is that it finds the first match or if no match, first value greater than the match.
so if there was no 60103 in your list it might show the zone for the next entry, 60104 or whatever was next highest.
questions? clear as mud?
Easy indeed!
