Archive for the ‘Uncategorized’ Category

Quick Transposer

A post from a Pistonhead member in need :

Hopefully someone can help!

I have a list of approx 50000 numbers in ‘Column A’ of spreadsheet. I need to split this list into batches of 1000, populated into Column B onwards.

Anyone with some VBA skill got any ideas? Thanks in advance!

My reply :
Sub QuickTranspose()

    Dim rng As Range
    Dim StartRowNum As Long
    Dim EndRowNum As Long
    Dim ColumnOffsetCounter As Long
    Dim GroupSize As Long
    Dim v
    Dim i As Long
    Dim TotalRows As Long
    GroupSize = 1000
    TotalRows = 50000
    'Dirty but without range names etc you just need to get the
    'selected cell to be the first one in the list
    Set rng = Application.Selection
    StartRowNum = rng.Rows.Row
    For i = 0 To TotalRows / GroupSize
        EndRowNum = StartRowNum + GroupSize - 1
        v = Range("A" & StartRowNum & ":A" & EndRowNum).Value
        Range(rng.Offset(0, i + 1), rng.Offset(GroupSize - 1, i + 1)).Value = v
        StartRowNum = EndRowNum + 1
    Next i
End Sub
Categories: Uncategorized

Watch Accuracy Tracker

I had an idea the other day that I would write a small app in Excel to keep track of all my watches.

I was planning to use a constant source like :

to keep track of the ‘real’ time and then have an ability to take a ‘snapshot’ of what the time is on each watch in a list ( that can be added to of course). This way using the inbuilt goodies of Excel all manner of graphs and stats can be applied to the raw data and people can see how their watch is behaving.

I was also thinking of adding a few features like resting orientation to see if any of the theories about leaving the watch 3 down, 9 down , dial up, dial down do influence the readings. Along with whether the chrono is running, is it being worn during the timing period etc.

Would anyone be interested in a copy of this? If anyone has any ideas / suggestions that would be great.

I am also thinking of using this as a quide too :

Categories: Uncategorized

G Challenge II – Roman Numerals

Ok, in quick sucession here is another G Challenge.  I think this one might be a bit of a stinker though!

II  (2) ;p   Functions that can be called from Excel to convert a number to its Roman equivalent, and also back from Roman to Normal.

So :

2010 -> MMX

Too easy… ?

231241 -> ccxxxMCCXLI

I have yet to attempt this myself as I normally set challenges from something I have had to do in the past I have found tricky.  This came from me choosing to put the challenge number as II not 2, and it popped into my head!

Edit:  I *know* that ROMAN() exists but that only goes up to 3999, and I am interpreting this as ‘classic’ numerals.    I think having looked at it that this is a massive challenge for a learner, but I have posted it now and we can always come back to it.   I’ll need something more sympathetic for GC III

Categories: Uncategorized

G Challenge I

November 25, 2010 3 comments

Ok, my friend is trying to learn Excel VBA and get into RAD development.   I am going to set a number of challenges which I am going to call a ‘G Challenge’  😛

So the first.  Take a string in the format :


where the format is :


In VBA I would like 2 things. 

  1. The string back in the format dd-mmm-yy hh:mm
  2. An actual date serial

Example above would go to 

  1. 24-Nov-2010 17:06:15
  2. 40506.7126736111

Go!   I would use 2 seperate functions to start off with.

Answers will be forthcoming in a post G Answer I

New Financial Product Today – PRD & Callable PRD

After all my interviewing ( more on that to come ) I have a new contract. As such, a new bank and a new lot of things to learn. So while watching a training video I cam across a mention of PRD’s and callable PRD’s. I just had to look it up. Google didn’t yield as much, but trusty ol’ Wikipedia did the job as per usual.

Power Reverse Dual – there we go.

Categories: Uncategorized Tags:

Further Excel VBA RAD Interview Questions

I had an interview today.  I didn’t do all that well at all and I got a little bit schooled.

This always seems to happen on my first technical interview with any serious technical ‘harrasment’.  I also have a load of knowledge tucked away in my brain, none of which the guy wanted to know about sadly.

Here are the things he did want to know about that I really got flustered and couldn’t answer.  Embarrasingly I gave myself an 8 out of 10, when I actually performed like a 5.5.  I think he rated himself as an 8, and then quite easily proceeded to destroy me. 😦  boo.

So… back to the hard core technical stuff for me as I spent too long this week on the finance side and not enough on the actual RAD side, silly me.

Ranges:  How do you create a range that will grow as and when data is enetered into it.

  • Name the 5 types of join on a database
  • how can I get a range to grow when data is added ( VBA & Excel)
  • How can I change the Last Used cell in Excel.
  • C++ pointers
  • Basic C# questions.

Ooh, and make sure that you can eloquently discuss the previous projects you have had on your CV.  Especially be prepared to get right into the nitty gritty.  I was silly enough to let information like the usings and data types etc I was using in one of my projects I only stopped working on about 8 months ago.  Not great either.

If I have a column with data in it.




and I want to add in another item of data, but have the range grow.  What can I do.

5 Types of Join – I was going to write the answer in here, but now I realise that what I need to do… is have a post dediected to SQL.  Any decent RAD developer is going to have to dig around in a database from time to time.  I think the problem I had is that I let the info go too stale, and didn’t keep on top of it.  I mean sure I seriously believe 100% I could do the job.  I would merely look in w3schools / Wikipedia for a few minutes and then be away and fine.  However… interviews just aren’t like that.  You need a decent answer, confident and concise right there, at the time to give the person that warm fuzzy glow that gets one hired.

Snippet :

Here are the first few things I selected to look at.   A simple glance over these for 30-90 mins before the interview would  have given me a massive boost in the interview:

Summary ( for my own revision )

Dynamic Ranges:

Last Used Cell:

Run a small macro.. ActiveSheet.UsedRange.   Or you can do this in the Immediate window if you don’t want it to be anywhere in the code.

Categories: Uncategorized

Financial RAD Developer

Categories: Uncategorized