#more-69197" class="postlink">Rob Schneider a derpy derp. Derp derp a derpity derp.

Alright! My Braun electric toothbrush has been returned - turns out it was the charger, not the unit itself. They replaced it for free. Of course this means that the battery in the unit itself will die now when the warranty runs out.

Bleeeeeuuuugh. Woke up with a head absolutely filled with a cold and a throat that feels like I've been drinking acid. Felt fine yesterday.

So I've got a glass of squash and a copy of Modem Woofare three rented from Blockbuster (rented a game! from Blockbuster! Sweet Jebus!) to keep me company. I fear the game might well make me sicker.

Also, the guy in Blockbuster looked vaguely like Dimrill and kept calling me 'buddy'. If I wasn't so deliriously sick I would have said "I'm not your buddy, guy."

Everyone will be happy to know that my VBA skills are fast approaching 1337. Almost.

I've knocked c20 minutes off the time it takes to do something so far by writing stuff.

once I realsied that all I need to do is a series of linked actions, it's fine. I'm cheating a bit by using the macro recorder, then reading the code, but we all do that, don't we? Don't we?

Someone no-one likes is leaving my office soon. His leaving card/collection has been stuck for two days on the desk of a colleague who's out of the office. I'm pretending I haven't seen it.

Stay strong Doc. I've stopped giving to work collections purely on the basis that they seem to happen every week (and I don’t like anyone I work with).

Yeah, I had something similar yesterday and still today.

I just got my marks from my exam.. I got 25 and needed 26 to pass. I am not happy!!

I am so fucking bored. And tired. And hungover. Mainly hungover.

Sorry to hear that.

Bad luck.

Was the question you failed about the initials?

That was a different exam

Cheers chaps... I am not sure if I want to resit again

Tell them no-one sane would use 26 as a pass mark, so there must be an off-by-one error somewhere.

I know.. one mark!!! bah!

I've written 109 lines of code today (OK, I've left a lot of notes in it, but y'know, they need to be there for quick and easy reference). As a rough skelton of what I want it to do, it's almost there. I'm far too pleased with myself than I have any real right to be. The enxt hurdles are going to be: making it open the correct Excel Files at the beginning, and some other things. I won't show any of it, as you'll tell me to use Ruby and mock me.

EDIT: What takes me about 35 minutes to do, now takes 3 seconds, if that, using the thingymabob

And that's all that matters, really. Good work!

Thanks, the problems I'm forseeing in the future are if I have a smaller or larger data set than I currently do. Everything is in the same column, all the time, it's just the number of rows changes week by week. I've done a messy work-around by using rows 2:5000 which will more than cover it, but 'm going to have to try it on another week's data soon. Ulp. I'm having to reference several different workbooks rather than trying to array the data (?) , but that'll come once I can visualise how the program should look in my head it ould be better. KNowing which steps to take when seems the most tricky part, then I just work out what hose steps should be.

I just bought a sandwich and a bottle of Coke from Morrisons.

The total came to £3.15.

I put £3.15 in coinage into the self-service checkout.

It counted it as £3.16 and gave me a penny back.

If your data set is going to change then you can code your solution to either (a) have the data locations/ranges as constants somewhere at the start so it's relatively painless to change them on demand or (b) if the format of the data is going to be solid enough then the code itself can examine the data and decide where everything is.

As a trivial example of the latter, if your data set is of variable length then, right at the start, set a counter and iterate through the rows from 1 onwards doing a simple check to see if there's any data in each row until you get to a blank row. Then you know that you've got, say, 99 rows of data and you can use that value throughout the code to process whatever's actually there. That way you never need to worry about how much data you feed it because it'll just automatically work out what's there and deal with it accordingly. As long as the actual format of the data you're dealing with is solid then there's no reason you should ever have to manually bugger about with this kind of thing or, as you're doing, set a huge limit somewhere 'just to be on the safe side'.

Thanks, I thought about doing that, but havwen't yet learnt to i) make a counter or ii) see if there is data there. I'll have a look in book to see if it can help, but it isn't great. I might start a new thread, actually, where you can laugh at my eager ineptitude.

EDIT: I'll get a better book, with a list of commands in it, then string them together.

Fucking Hell!

Cool story, bro.

I once found a fiver hanging out of a self-service machine. I took it, even though I was only walking by.

From memory-ish:




When the While loop finishes RowCount will hold the value of the last row of actual data in the spreadsheet. This assumes that you've always got data in column 1, if that's not the case then just change the y position value in both of the Cells commands to be whatever column you want to do your check in.

Thanks, I'll give it a whirl.

In a country of 250+ million people, when "at least two people" write in, surely the correct response is to laugh and throw the letters in the bin?

I know!!

Careful, you might automate yourself out of a job. Best take the comments out and introduce a random subroutine that turns it all purple or sonething which only you can take over.

(And yes, I'm just jealous of your VBA skills: I currently have a series of routines which could, with tweaking, do AMAZING things, but have yet to apply them to my current project)

Best tip form my book so far: Use the macro recorder to,um, record a macro, then ALT F11 to have a look at it in the editor. Then remove lines at random, by sticking an apostrophe in front of them, and see if it still works.

This is pretty much how I learnt, and it seems to get me by.
Unless you have a stupid looking spreadsheet with data all over the place, you should be able to get Excel to tell you the last used row, without having to iterate through all the rows to check whether or not they're populated.

Something like

will pass the last used cell in column a (so you could swap that for a different column if it helps) to the lastrow variable and you can then make use of that wherever the macro has used the specific range.

So if your code says something like:

you could change it to

and it should always pick up the right number of rows.

Thank you very much, sir!

Happy Birthdays, Craig and Lewie!

Sore throat and congestion now with added fever!

with cake

Troll Hunter for £5? Kinda made the trip to ASDA worth it.

First Google image for 'craig lewie cake' gives this...


Close enough.

Happy Birthday.

Office collection maths is insane. There are about 60 people in my office. Everyone always gets something to the value of about £20-25. Clearly, the majority of people aren't putting in.

But when the birthday victim brings the cakes in, everyone chows down. It's crooked.

I don't understand the idea of giving someone money because they’re leaving. Fuck that right off. Buy them a present and give them a card, perhaps. Wads of cash? No.

One of the HOCs left, and we bought her an ice-cream maker.
Two years later she came back to work for us, and I demanded it back.

People get money?! I'm in the wrong game.

I work with one curmudgeon who will only donate to a collection if it's for someone on the same pay scale as him, or below. The way he sees it, 'they can fookin' afford to buy themselves a present'.

Oh, man. Sorry to hear that.

Happy Birthday Craig and Lewie!

My mum and my niece and nephew texted me a picture of a park bench they bought for the garden in the park my brother and I grew up next to. Aside from them not getting the plaque right (they've put "In Loving Memory, from Mum, Nicole, Dylan and brother Michael" which makes me sounds like a fucking monk) it is really, really, really weird to see your own brothers name on a park bench.

(That hasn't been scratched there and followed by the words "luvs Shaz" or some such, anyway.)

In my place, the collection is usually turned into a gift by whomever knows the person best. If you get cash, it means your a billynomates.

I got cash when I left.

Srsly?

You use 'whomever' and then fuck up your/you're? Hand that PhD back on your way out

I'd rather get cash than some piece of tat.

Particularly with the reason why you are leaving.

Indeed. I'm curious as to who'll get nominated as my 'person' (assuming, as per DocG, anyone actually likes me).

The best leaving gift I ever got was my Homer Simpson beer tankard which holds all but the biggest cans of beer and is extremely robust.

When I left my job in April to start doing a hand made arts and crafts stall they bought me a whole load of craft making equipment and materials. Considering I'd only been perm there for 3 months that was pretty awesome and meaningful!